Generate multiple rows for single column

Blue pink 1 Reputation point
2022-10-03T17:49:23.443+00:00

I’ve data as below:
Create table #student(Student_ID int, Name varchar(20))
Insert into #student values(1, ‘A’)
Insert into #student values(2, ‘B’)
Create table #Test(Student_ID int, Test_Date datetime, Test_Type varchar(20))
Insert into #Test values(1, ‘2/1/2022’, ‘ENG’)
Insert into #Test values(1, ‘3/1/2022’, ‘Science)
Insert into #Test values(1, ‘4/1/2022’, ‘Math)
Insert into #Test values(2, ‘10/1/2022’, ‘Science)
Insert into #Test values(2, ‘10/2/2022’, ‘Math)
Insert into #Test values(3, ‘1/2/2022’, ‘Math)
Need to select in this format:

247143-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Tom Cooper 8,461 Reputation points
    2022-10-03T19:21:57.297+00:00
    ;With cte As  
    (Select s.Student_ID, s.Name, t.Test_Date, t.Test_Type,  
      Row_Number() Over(Partition By s.Student_ID Order By t.Test_Date) As rn  
    From #student s  
    Inner Join #Test t On s.Student_ID = t.Student_ID)  
    Select Case When rn = 1 Then Cast(Student_ID As varchar(11)) Else '' End As 'Student Id',  
      Case When rn = 1 Then Name Else '' End As 'Name',  
      Convert(char(10), Test_Date, 101) As 'Test Date',  
      Test_Type As 'Test Type'  
    From cte  
    Order By Student_ID, Test_Date;  
    

    Tom

    0 comments No comments