Sql Column Grouping

Question

Possible Duplicate:
Concatenate many rows into a single text string?

I have a table called SkillSets with a SkillID field and a SkillSetID field. Let's says I have records in this table as in:

SkillID  SkillSetID
-------  ----------
750      2
750      4
750      5
751      3
751      4
751      6

I need a query that produces the following:

SkillID  SkillSetIDs
-------  ----------
750      2,4,5
751      3,4,6

Seems to me I remember seeing this using some sort of xml clause but cant find anything about it, and just don't know how to get it done without using a cursor or while loop. Can you help?

You need to do a string concatenation aggregation operation. This is highly database specific. Which engine are you using? — Gordon Linoff, Dec 17 '12 at 21:39
If you're using TSQL: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Bort, Dec 17 '12 at 21:41
Sql Server 2008 R2. So, nothing rings a bell using some sort of XML clause? Thanks for the reply. — David Whitten, Dec 17 '12 at 21:44

score 0 · Answer 1 · answered Dec 17 '12 at 21:39

0

In MySQL, this would work:

SELECT SkillID, GROUP_CONCAT(SkillSetID) As SkillSetIDs
FROM skills
GROUP BY SkillID

But GROUP_CONCAT is MySQL specific, so if you are using a different database, that won't fly.

answered Dec 17 '12 at 21:39

Eric Petroelje

59,820
9
127
177

score 0 · Answer 2 · answered Dec 17 '12 at 21:47

does this help you out?

select 'test' as Test, 1 as Item 
into #test 
union select 'test2', 2 
union select 'test', 3 
union select NUll, 4 
union select 'test', 5 

select t2.test, STUFF((SELECT  ', ' + cast(t1.Item as varchar (10) )
        FROM #test t1 where t2.test = t1.test 
        FOR XML PATH('')), 1, 1, '') 
     from #test t2
     group by t2.test

score 0 · Answer 3 · answered Dec 17 '12 at 22:00

Query:

SQLFIDDLEExample

SELECT
s.SkillID,
STUFF((
        SELECT ',' + SkillSetID
        FROM SkillSets s2
        WHERE s2.SkillID = s.SkillID
        FOR XML PATH('')
      ), 1, 1, '')  AS SkillSetIDs
FROM SkillSets s
GROUP BY s.SkillID

Result:

| SKILLID | SKILLSETIDS |
-------------------------
|     750 |       2,4,5 |
|     751 |       3,4,6 |

Sql Column Grouping

3 Answers3