December 9, 2009 at 3:13 pm
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
December 9, 2009 at 8:00 pm
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
December 9, 2009 at 10:46 pm
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
December 9, 2009 at 11:21 pm
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
December 10, 2009 at 12:30 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply