How to do a Grouped, Comma Separated List

Question

Post reply

How to do a Grouped, Comma Separated List

FredS-1001785

Mr or Mrs. 500

Points: 564
More actions
December 9, 2009 at 3:13 pm

#385921

I would like to build a list comma separated values of strings, but I also need to group the answers. I can do it if I select where F2 = 1 and produce a list. I can also see how to do this with a CURSOR, but I would prefer to avoid that.

Data

F1 F2

A 1

B 1

C 1

D 2

A 2

J 2

A 3

B 3

Want

F1 List

1 A,B,C

2 D,A,J

3 A,B

Thanks Fred

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Hi Fred,

I think you got F1 and F2 mixed up a little in your desired output, but no worries... this will do it...

--===== Create and populate a test table. This is for



     -- demonstration only... it is not a part of the solution.

     IF OBJECT_ID('TempDB..#YourTable','U') IS NOT NULL

        DROP TABLE #YourTable --Just to make rerunning tests easier

 SELECT *

   INTO #YourTable

   FROM (SELECT 'A',1 UNION ALL

         SELECT 'B',1 UNION ALL

         SELECT 'C',1 UNION ALL

         SELECT 'D',2 UNION ALL

         SELECT 'A',2 UNION ALL

         SELECT 'J',2 UNION ALL

         SELECT 'A',3 UNION ALL

         SELECT 'B',3) d (F1, F2)

--===== Now, solve the problem in a high performance, set based manner

 SELECT yt1.F2,

        List = STUFF((SELECT ','+CAST(F1 AS VARCHAR(10))

                        FROM #YourTable yt2

                       WHERE yt2.F2 = yt1.F2

                         FOR XML PATH(''))

               ,1,1,'')

   FROM #YourTable yt1

  GROUP BY yt1.F2

As a side bar (especially since you're relatively new here), I recommend you read the article found at the first link in my signature line below. People will trip over themselves trying to help you if you post using the methods in the article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Now... I helped you. Please help me. Please explain what the business requirement is to denormalize the data in such a fashion. In other words, why does this need to be done? Looking for something a little better than "they want it that way", if you know what I mean.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

FredS-1001785 Mr or Mrs. 500 Points: 564 More actions · Answer 3

Thanks for the help.

I use it to display data for users. For instance, if we have three different customers asking us to follow information on a supplier I display the three customer names in a comma separated list to make the display a single entry.

This it faster for the operator to scan with their eye for what they are looking for.

Fred

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

FredS-1001785 (12/9/2009)

This it faster for the operator to scan with their eye for what they are looking for.

Ah... finally... someone with a practical reason. Thanks, Fred.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions