Generate multiple rows for single column-CodePudding

I've data as below:

Create table #student(id int, name varchar(20))
create table #test(id int, test_Date datetime, test_type varchar(20))
Insert int #student values (1, 'A')
insert into #student values (2, 'B')
insert into #student values (3, 'C')

insert into #test values (1, '1/1/2022', 'Math')
insert into #test values (1, '1/2/2022', 'Eng')
insert into #test values (1, '1/3/2022', 'Science')

insert into #test values (2, '2/1/2022', 'Math')
insert into #test values (2, '2/2/2022', 'Eng')

insert into #test values (3, '3/1/2022', 'Math')
insert into #test values (3, '3/2/2022', 'Science')

Need data in the below format: Output

CodePudding user response：

Looks like you simply just want to join your #student table to your #test table.

    SELECT s.id, s.name, t.test_date. t.test_type 
    FROM #student s
    JOIN #test t
      ON s.id = t.id
   ORDER BY s.id, t.test_date, t.test_type

This will show the id, name, test date and test type per student. Ordered by the student id, test date and test type.

CodePudding user response：

try this

;with t0 AS (
SELECT s.id, s.name, t.test_date, t.test_type
,dense_rank() over(partition by s.id order by s.id ,test_date) AS drid
,dense_rank() over(partition by s.id,name order by s.id ,test_date) AS drname
FROM #student s
JOIN #test t
ON s.id = t.id
)
 select case drid when 1 then id else null end as id
 ,case drname  when 1 then name else null end as 
 name,test_Date,test_type from t0
 ORDER BY t0.id, t0.name