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

stuff input
;
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

stuff output

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s