Generate multiple rows for single column

Transact-SQL
1

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:
image

Thanks

2

Typically you'd prefer to use some type of reporting/presentation tool to get this format, but if you want to do it in SQL then:


;WITH cte_results AS (
    SELECT t.id, t.test_Date, t.test_type, s.Name,
        ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY test_date) AS row_num
    FROM #test t
    INNER JOIN #student s ON s.id = t.id
)
SELECT 
    CASE WHEN row_num = 1 THEN CAST(id AS varchar(10)) ELSE '' END AS id,
    CASE WHEN row_num = 1 THEN Name ELSE '' END AS Name,
    test_Date,
    test_type    
FROM cte_results
3

When there are many columns like Id, Name and don't want to repeat all the columns data. Suppose I'm selecting 10 columns. Out of 10, 8 are like Id and name and last two columns test type and test date columns have multiple rows.
Is there any easy way to do this?

4

I answered the q you originally asked. Doing this in SQL is not especially easy, SQL wasn't designed to do this.