I want distinct numbers with choice as columns using datetime ascending order

Question

there is a table with

1001  vsp,science,BBA  25-05-2020
1001  vsp,Maths,Btech  26-05-2020
1001  vsp,Maths,Btech  27-05-2020
1002  hyd,science,BBA  24-05-2020
1002  blr,Maths,Btech  25-05-2020

I want

1001  vsp,science,bba   vsp,Maths,Btech    vsp,Maths,Btech 

Answer 1

You need one of my favorite combo to achieve your goal:

• CTE (to create proper sub request): https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
• ROW_NUMBER (to rank your rows): https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
• PIVOT (to pivot your results): https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

And now the solution:

WITH orderedCourse AS
(
    SELECT  GroupId,
            CourseLabel,
            ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY CourseDate) AS CourseNumber
    FROM @myCourses
)
SELECT TOP (1) GroupId, [1], [2], [3], [4]
FROM    
        (
            SELECT  GroupId,
                    CourseLabel,
                    CourseNumber
            FROM orderedCourse
        ) AS src
        PIVOT
        (
            MIN(CourseLabel) -- default agregate
            FOR CourseNumber IN ([1], [2], [3], [4] /*... if you have more courses by group*/)
        ) AS pvt
ORDER BY GroupId

Which give the result:

1001  vsp,science,BBA  vsp,Maths,Btech  vsp,Maths,Btech  NULL

I used this code to declare the table:

INSERT INTO @myCourses
SELECT 1001, 'vsp,science,BBA', CAST('25-05-2020' AS date) UNION ALL
SELECT 1001, 'vsp,Maths,Btech', CAST('26-05-2020' AS date) UNION ALL
SELECT 1001, 'vsp,Maths,Btech', CAST('27-05-2020' AS date) UNION ALL
SELECT 1002, 'yd,science,BBA', CAST('24-05-2020' AS date) UNION ALL
SELECT 1002, 'blr,Maths,Btech', CAST('25-05-2020' AS date);

SELECT  GroupId,
        CourseLabel,
        CourseDate,
        ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY CourseDate) AS CourseNumber
FROM @myCourses;