SQLSERVERLEARNER

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:
[sql]

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
[/sql]

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.
[sql]
–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
[/sql]