Convert multiple rows to single column

To convert multiple rows to single column.

 

At SQL Server database this can be achieved using STUFF function.

The STUFF function inserts a string into another string.

 

Run below queries to get an idea

 

—–Query Start————

create table #temp
(
personid int,
name varchar(10),
subjects varchar(10)
)
;
insert into #temp values(1, ‘mike’, ‘maths’)
insert into #temp values(1, ‘mike’, ‘science’)
insert into #temp values(1, ‘mike’, ‘social’)
insert into #temp values(2, ‘jay’, ‘economics’)
insert into #temp values(2, ‘jay’, ‘history’)
insert into #temp values(3, ‘joe’, ‘maths’)
insert into #temp values(4, ‘kelly’, ‘geography’)
insert into #temp values(4, ‘kelly’, ‘computers’)
insert into #temp values(5, ‘mike’, ‘literature’)
;
select * from #temp

;
SELECT personid, name,
subjects = STUFF(
(SELECT ‘, ‘ + b1.subjects FROM  #temp b1 where b1.personid = b2.personid and b1.name = b2.name FOR XML PATH (”)), 1, 1, ”
)
FROM #temp b2   GROUP BY b2.personid, name

 

create table #temp
(
personid int,
name varchar(10),
subjects varchar(10)
)
;
insert into #temp values(1, 'mike', 'maths')
insert into #temp values(1, 'mike', 'science')
insert into #temp values(1, 'mike', 'social')
insert into #temp values(2, 'jay', 'economics')
insert into #temp values(2, 'jay', 'history')
insert into #temp values(3, 'joe', 'maths')
insert into #temp values(4, 'kelly', 'geography')
insert into #temp values(4, 'kelly', 'computers')
insert into #temp values(5, 'mike', 'literature')
;
select * from #temp

;
SELECT personid, name,
subjects = STUFF(
(SELECT ', ' + b1.subjects FROM #temp b1 where b1.personid = b2.personid and b1.name = b2.name FOR XML PATH ('')), 1, 1,''
)
FROM #temp b2 GROUP BY b2.personid, name

reference:

https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server