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.<\/p>\n
Sample data creation:
\n[sql]<\/p>\n
CREATE TABLE GroupMembers(GroupName VARCHAR(100),MemberName VARCHAR(100))
\nGO<\/p>\n
INSERT INTO GroupMembers
\nSELECT ‘Group1′,’A’
\nunion
\nSELECT ‘Group1′,’B’
\nunion
\nSELECT ‘Group1′,’C’
\nunion
\nSELECT ‘Group1′,’D’
\nunion
\nSELECT ‘Group1′,’E’
\nunion
\nSELECT ‘Group1′,’F’
\nunion
\nSELECT ‘Group1′,’G’
\nunion
\nSELECT ‘Group1′,’H’
\nunion
\nSELECT ‘Group2′,’A1’
\nunion
\nSELECT ‘Group2′,’B1’
\nunion
\nSELECT ‘Group2′,’C1’
\nunion
\nSELECT ‘Group2′,’D1’
\nunion
\nSELECT ‘Group2′,’E1’
\nunion
\nSELECT ‘Group2′,’F1’
\nunion
\nSELECT ‘Group2′,’G1’
\nunion
\nSELECT ‘Group2′,’H1’
\nGO
\nSELECT GroupName,MemberName FROM GroupMembers
\nGO
\n[\/sql]<\/p>\n
The above created table has data that belongs to two groups Group1 and Group2.<\/p>\n
Below TSQL Queries concatenate this data into single column\/row for each group.
\n[sql]
\n–Sol1:
\n–Using XML
\nSELECT GroupName,
\n(SELECT MemberName
\nFROM GroupMembers WHERE GroupName=A.GroupName
\nFOR XML PATH(”))
\nFROM GroupMembers A
\nGROUP BY GroupName
\nGO<\/p>\n
–Sol2:
\n–With out seperation between member names
\n–Using XML, Without XML Attributes
\nSELECT GroupName,
\n(SELECT MemberName AS [text()]
\nFROM GroupMembers WHERE GroupName=A.GroupName
\nFOR XML PATH(”))
\nFROM GroupMembers A
\nGROUP BY GroupName<\/p>\n
–Sol3:
\n–With comma seperation between member names
\n–Using XML, Without XML Attributes
\nSELECT GroupName,
\n(SELECT MemberName+ ‘,’ AS [text()]
\nFROM GroupMembers WHERE GroupName=A.GroupName
\nFOR XML PATH(”))
\nFROM GroupMembers A
\nGROUP BY GroupName<\/p>\n
–Sol4:
\n–With comma seperation between member names(no ending comma)
\n–Using XML, Without XML Attributes
\nSELECT G.GroupName,Left(G.Members,Len(G.Members)-1)
\nFROM (
\nSELECT GroupName,
\n(SELECT MemberName+ ‘,’ AS [text()]
\nFROM GroupMembers WHERE GroupName=A.GroupName
\nFOR XML PATH(”)) AS Members
\nFROM GroupMembers A
\nGROUP BY GroupName
\n) G
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"
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’…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,1789],"tags":[],"class_list":["post-253","post","type-post","status-publish","format-standard","hentry","category-sqlserverpedia-syndication","category-t-sql-code-examples"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/253","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=253"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/253\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=253"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=253"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=253"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}