Concatenating Data From Different Rows into Single Column Row

Posted on : 03-08-2012 | By : Devi Prasad | In : SQLServerPedia Syndication, T-SQL Code Examples

Share:

2


Consider a scenario where a table has multiple rows, and each of these rows belong to a group and the data from all the rows should be concatenated based on the group. This post explains different ways to accomplish this.

Sample data creation:


CREATE TABLE GroupMembers(GroupName VARCHAR(100),MemberName VARCHAR(100))
GO
 
INSERT INTO GroupMembers
SELECT 'Group1','A'
union
SELECT 'Group1','B'
union
SELECT 'Group1','C'
union
SELECT 'Group1','D'
union
SELECT 'Group1','E'
union
SELECT 'Group1','F'
union
SELECT 'Group1','G'
union
SELECT 'Group1','H'
union
SELECT 'Group2','A1'
union
SELECT 'Group2','B1'
union
SELECT 'Group2','C1'
union
SELECT 'Group2','D1'
union
SELECT 'Group2','E1'
union
SELECT 'Group2','F1'
union
SELECT 'Group2','G1'
union
SELECT 'Group2','H1'
GO
SELECT GroupName,MemberName FROM GroupMembers
GO

The above created table has data that belongs to two groups Group1 and Group2.

Below TSQL Queries concatenate this data into single column/row for each group.

--Sol1:
--Using XML
SELECT GroupName,
(SELECT MemberName 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) 
FROM GroupMembers A
GROUP BY GroupName
GO
 
--Sol2:
--With out seperation between member names
--Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName AS [text()] 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) 
FROM GroupMembers A
GROUP BY GroupName
 
--Sol3:
--With comma seperation between member names
--Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName+ ',' AS [text()] 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) 
FROM GroupMembers A
GROUP BY GroupName
 
--Sol4:
--With comma seperation between member names(no ending comma)
--Using XML, Without XML Attributes
SELECT G.GroupName,Left(G.Members,Len(G.Members)-1)
FROM (
SELECT GroupName,
(SELECT MemberName+ ',' AS [text()] 
FROM GroupMembers WHERE GroupName=A.GroupName 
FOR XML PATH('')) AS Members
FROM GroupMembers A
GROUP BY GroupName
) G
(Visited 142 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (2)

Hello Devi,
Nicely explained! we can also remove the xml attributes and get clean resultset using SUBSTRING function

–Sol5:

SELECT a.GroupName, SUBSTRING(
( SELECT ‘,’ + b.MemberName
FROM GroupMembers b
WHERE a.GroupName= b.GroupName
FOR XML PATH(”)
), 2, 10000000000)
FROM GroupMembers a
GROUP BY a.GroupName

Thanks Abhijit,

Your code works perfectly.

Write a comment