Blog » Via SQL SERVER LEARNER
Concatenating Data From Different Rows into Single Column Row
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 1
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 1
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 1
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH('')) AS Members
FROM GroupMembers A
GROUP BY GroupName
) G
very nice post, i undoubtedly adore this first-rate website, carry on it
very nice post, i undoubtedly adore this first-rate website, carry on it
Perfect work you have done, this website is actually cool with superb information .
Perfect work you have done, this website is actually cool with superb information .
For those who are unaware, Vistaprint is a website that has been around for years, and it offers a variety of personalized products that can be used for businesses,
gifts, weddings, and more. 5. Out of town guests will feel especially welcome if you
give them special attention at the reception.
For those who are unaware, Vistaprint is a website that has been
around for years, and it offers a variety of personalized products that can be used for businesses, gifts, weddings, and more.
5. Out of town guests will feel especially welcome if you give them special attention at the reception.
Hi there, everything is going well here and ofcourse every one is sharing information,
that’s really good, keep up writing.
Hi there, everything is going well here and ofcourse every one is sharing information,
that’s really good, keep up writing.
It’s fantastic that you are getting ideas from
this piece of writing as well as from our dialogue made at this place.
It’s fantastic that you are getting ideas from this piece of writing
as well as from our dialogue made at this place.
But thanks to several exercises, this skill can
be easily improved and developed through time. These will involve fast bounding movements such as
jumping onto boxes. It is the ability of one player to jump high to
be able to just drop the ball through the ten foot high rim.
But thanks to several exercises, this skill can be easily improved and
developed through time. These will involve fast bounding movements such as
jumping onto boxes. It is the ability of one player to jump high to be able to just drop the
ball through the ten foot high rim.
Thanks a lot for sharing this with all of us you really recognize what
you're speaking about! Bookmarked. Kindly additionally seek advice from my web site =). We will have a link trade agreement between us
Thanks a lot for sharing this with all of us you really recognize what you're speaking about! Bookmarked. Kindly additionally seek advice from my web site =). We will have a link trade agreement between us
Excellent site you have here but I was wanting to know if you knew of any message boards that cover the same topics talked about here?
I'd really like to be a part of group where I can get suggestions from other knowledgeable people that share the same interest. If you have any recommendations, please let me know. Many thanks!
Excellent site you have here but I was wanting to know if you knew of any
message boards that cover the same topics talked
about here? I'd really like to be a part of group where I can get suggestions from other knowledgeable people that share the same interest. If you have any recommendations, please let me know. Many thanks!
Hey there I am so glad I found your blog page, I really found you by error, while I was looking on Google for something else, Nonetheless I am here now and would just like to say kudos for
a incredible post and a all round exciting blog (I
also love the theme/design), I don’t have time to go through it all
at the minute but I have saved it and also included your
RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the excellent work.
Hey there I am so glad I found your blog page, I really found you by
error, while I was looking on Google for something else, Nonetheless I am here now and would just like to say kudos for a incredible post and a all round exciting blog (I also love the theme/design), I don’t have
time to go through it all at the minute but I have saved it
and also included your RSS feeds, so when I have
time I will be back to read a great deal more, Please do keep up the
excellent work.
Hey there I am so happy I found your weblog,
I really found you by error, while I was researching on Aol for something
else, Regardless I am here now and would just like to say thanks
for a tremendous post and a all round interesting
blog (I also love the theme/design), I don’t have time to look
over it all at the minute but I have saved it and also added
your RSS feeds, so when I have time I will be back
to read a lot more, Please do keep up the great work.
Hey there I am so happy I found your weblog, I really found you by error, while
I was researching on Aol for something else, Regardless I am here now and would just like to say thanks for a tremendous post and a all round interesting blog (I
also love the theme/design), I don’t have time to look over it all
at the minute but I have saved it and also added your
RSS feeds, so when I have time I will be back to read a lot more,
Please do keep up the great work.
I know this if off topic but I'm looking into starting my own blog and was wondering what all is needed to get set up? I'm assuming having a
blog like yours would cost a pretty penny?
I'm not very internet savvy so I'm not 100% sure. Any tips or advice would be greatly appreciated. Many thanks
I know this if off topic but I'm looking into starting my own blog and was wondering what all is needed to get set up? I'm
assuming having a blog like yours would cost a pretty penny?
I'm not very internet savvy so I'm not 100% sure. Any tips or advice would be greatly appreciated. Many thanks
Right here is the right website for anybody
who really wants to understand this topic. You know a whole lot its almost tough to argue with you (not that I
actually will need to…HaHa). You certainly put a new spin on a topic that has been written about
for years. Wonderful stuff, just wonderful!
Right here is the right website for anybody who really wants to understand this topic.
You know a whole lot its almost tough to argue with you (not that I actually will need to…HaHa).
You certainly put a new spin on a topic that has been written about for years.
Wonderful stuff, just wonderful!
Wonderful, what a blog it is! This weblog presents helpful facts to us, keep it up.
Hello there! I could have sworn I've visited this blog before but after browsing through some of the posts I realized it's new to me.
Regardless, I'm definitely happy I discovered it and I'll
be bookmarking it and checking back frequently!
Hello there! I could have sworn I've visited this blog before but after browsing through some of the posts I realized it's new to
me. Regardless, I'm definitely happy I discovered it and I'll be bookmarking it and
checking back frequently!