Click here to Skip to main content
14,173,165 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi,

I have a table consisting of 2 columns

id & colour

the values for these are as follows:-

id color
1 black
1 white
1 blue
2 red
3 magenta
3 purple

The output required is :-
id colour
1 black;white;blue
2 red
3 magenta;purple

Please help.

What I have tried:

I have tried using stuff function using comma separated.
Posted
Updated 15-May-19 8:35am
Comments
OriginalGriff 15-May-19 14:24pm
   
Can't seem to post as a solution for some reason ... lets try as a comment...

Try this:
SELECT ID, 
       Color = STUFF((SELECT ', ' + Color
                      FROM MyTable b 
                      WHERE b.Id = a.Id 
                      FOR XML PATH('')), 1, 2, '')
FROM MyTable a
GROUP BY Id
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You are on the right track with STUFF, and what you need to do is to put that into a SubQuery

Proof of Concept
DECLARE @table TABLE (ID int, color varchar(16))

INSERT @table VALUES (1, 'black'),  ( 1, 'white'),  ( 1, 'blue'),  ( 2, 'red'),  ( 3, 'magenta'),  ( 3, 'purple')

SELECT  t.ID
  ,     Colors = STUFF(
          (  SELECT  ', ' + s.color 
             FROM    @table s
             WHERE  s.ID = t.ID
             FOR XML PATH('')
          ), 1, 1, ''
        )

FROM      @table t
GROUP BY  t.ID
ORDER BY  1
Which returns
ID  Colors
==  ===================  
1   black, white, blue
2   red
3   magenta, purple
   
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this:
SELECT ID, 
       Color = STUFF((SELECT ', ' + Color
                      FROM MyTable b 
                      WHERE b.Id = a.Id 
                      FOR XML PATH('')), 1, 2, '')
FROM MyTable a
GROUP BY Id
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Several methods documented here:
Concatenating Row Values in Transact-SQL - Simple Talk[^]

Eg:
SELECT
    t1.id,
    STUFF
    (
        (
            SELECT ';' + t2.color
            FROM YourTable As t2
            WHERE t2.id = t1.id
            ORDER BY t2.color
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'),
        1, 1, ''
    ) As color
FROM
    YourTable As t1
GROUP BY
    id
;
How Stuff and 'For Xml Path' work in Sql Server - Stack Overflow[^]

Or, if you're using SQL Server 2017 or later:
STRING_AGG (Transact-SQL) - SQL Server | Microsoft Docs[^]
SELECT
    id,
    STRING_AGG(color, ';') As color
FROM
    YourTable
GROUP BY
    id
;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190524.3 | Last Updated 15 May 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100