Alternative ways for Comma separated grouping

Question

Post reply

Alternative ways for Comma separated grouping

vignesh.ms

Hall of Fame

Points: 3456
More actions
April 18, 2014 at 3:44 am

#306880

create table #mytable (id int identity,[group] varchar(100),decision varchar(100),category varchar(100))
Insert into #mytable values ('1111','yes','A')
Insert into #mytable values ('1111','yes','B')
Insert into #mytable values ('2222','no','A')
Insert into #mytable values ('3333','yes','A')
Insert into #mytable values ('3333','yes','C')
Insert into #mytable values ('4444','no','A')
Insert into #mytable values ('4444','no','B')
Output Query :
SELECT categories, COUNT(*) count
FROM (
SELECT
STUFF((SELECT ',' + mt.category
FROM mytable mt
WHERE m.[group] = mt.[group]
ORDER BY mt.category
FOR XML PATH(''), TYPE).
value('.', 'NVARCHAR(MAX)'), 1, 1, '') categories
FROM mytable m
GROUP BY [group]
) z
GROUP BY categories;
I got this code somewhere from internet & I like to know alternative ways to do this ... Is there any other better way ??
also explain how does above query works ???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

quick Google....heres an article that may help

http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with-for

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 2

vignesh.ms (4/18/2014)
also explain how does above query works ???

SQL Server Books Online is your best friend. If you don't have a local copy, download it from Microsoft.

Click Index and search XML [SQL Server]. If you have questions on the BOL material, post them here and we'll do our best to answer them.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

J Livingston SQL (4/18/2014)
quick Google....heres an article that may help
http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with-for

JLS, I can't get that link to load. It just spins and spins. From the url is it using FOR XML like the example already posted here or is there another approach being used. I am curious because using FOR XML to generate a comma separated list is the easiest and fastest way I know of.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Sean Lange (4/18/2014)
J Livingston SQL (4/18/2014)
quick Google....heres an article that may help
http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with-for
JLS, I can't get that link to load. It just spins and spins. From the url is it using FOR XML like the example already posted here or is there another approach being used. I am curious because using FOR XML to generate a comma separated list is the easiest and fastest way I know of.

odd...worked earlier...all I get now is spins as well ???...all it was an explanation of how XML path works in easy steps.....which the OP could find via googlefoo 😀

It wasn't a better method.