Combine multiple rows into one row

Transact-SQL
1

I have the following in my table:
create table #table1(id int,
student varchar(50),
code varchar(50),
desc varchar(50),
)
insert into #table1
select 123, 'studA', '10', 'French'
select 123, 'studA ', '20', 'Spanish'
select 234, 'studB', '30', 'English'
select 345, 'studC ', '20', 'Spanish'
select 345, 'studC ', '10', 'French'

id student code desc
123 studA 10 French
123 studA 20 Spanish
234 studB 30 English
345 studC 20 Spanish
345 studC 10 French

The output should be:
id student code1 desc1 code2 desc2
123 studA 10 French 20 Spanish
234 studB 30 English
345 studC 20 Spanish 10 French

Thanks for any help.

2 
;WITH cte AS
(
select id,student,code,[desc],
   ROW_NUMBER()OVER(PARTITION BY id ORDER BY (select null)) as rn
from #table1 as t
)
--select * from cte
,cteRN AS
(
select id,student,code,[desc],rn,(rn-1)/2 as joinRn, rn%2 as restRn
from cte
)
--select * from cteRN


Select isnull(a.id,b.id) as id 
    ,isnull(a.student,b.student) as student
    ,a.code,a.[desc]
    ,b.code,b.[desc]--,a.rn,b.rn,a.restRn,b.restRn
from (select id,student,code,[desc],rn, joinRn from cteRN where restRn = 1 ) as a
    full join (select id,student,code,[desc],rn, joinRn from cteRN where restRn = 0 ) as b
    on a.id = b.id
    and a.joinRn = b.joinRN
    and a.rn <> b.rn
order by isnull(a.id,b.id)

here is the output:

id student code desc code desc
123 studA 10 French 20 Spanish
123 studA 30 English null null
234 studB 30 English null null
345 studC 20 Spanish 10 French

dbfiddle here

2 Likes