Sql query to get the values separated by comma

Question

0.00/5 (No votes)

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 15-May-19 7:32am

chints786

Updated 15-May-19 8:35am

Add a Solution

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MadMyche · Answer 1 · 2019-05-15T08:32:00

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

Proof of Concept

SQL

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

OriginalGriff · Answer 2 · 2019-05-15T08:34:00

Solution 2

Try this:

SQL

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

Posted 15-May-19 8:34am

OriginalGriff

Richard Deeming · Answer 3 · 2019-05-15T08:36:00

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

Eg:

SQL

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[^]

SQL

SELECT
    id,
    STRING_AGG(color, ';') As color
FROM
    YourTable
GROUP BY
    id
;

Sql query to get the values separated by comma

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0