Get comma separated list of values - SQL

Question

products

-----------------------------
| ProductId | ModelId       |
|---------------------------|
| 12345     | A3666         |
| 12345     | A3667         |
| 12345     | A8999         |
| 12346     | A3666         |
| 12346     | A3667         |
-----------------------------

models

-----------------------------
| ModelId   | Name          |
|---------------------------|
| A3666     | win           |
| A3667     | xia           |
| A8999     | vor           |
-----------------------------

I'm trying to get the output this way:

-----------------------------------
| ProductId | Models              |
|---------------------------------|
| 12345     | win,xia,vor         |
| 12346     | win,xia             |
-----------------------------------

My code is :

SELECT
    p.ProductId,
    STUFF
    (
        (
            SELECT ',' + Name
            FROM models m
            WHERE m.ModelId=p.ModelId
            ORDER BY Name
            FOR XML PATH(''), type
        ).value('.', 'varchar(max)'), 1, 1, ''
    ) AS ModelNames
FROM
    products p

which gives the o/p :

-----------------------------
| ProductId | ModelNames    |
|---------------------------|
| 12345     | win           |
| 12345     | xia           |
| 12345     | vor           |
| 12346     | win           |
| 12346     | xia           |
-----------------------------

where am i going wrong. [I think the question was clear enough to be understood but SO wouldnt submit as it says, add more details, mostly code. hence this text.]

I don't know why people are so obsessed with using CSV to transfer data in and out of SQL Server. SQL Server has at least two data types (tables and xml) that are *designed* to hold multiple values. Unlike strings for which you have to write all kinds of odd code to either construct them (as here) or rip them apart to get the values back out. And lose any data type checking that you might have usefully obtained. — Damien_The_Unbeliever, Nov 05 '14 at 07:16

score 1 · Answer 1 · answered Nov 05 '14 at 06:59

1

I think this should solve your issue.

SELECT DISTINCT
    p.ProductId,
    STUFF
    (
        (
            SELECT ',' + m.Name
            FROM models m
            INNER JOIN products ip
            ON m.ModelId = ip.ModelId
            WHERE ip.ProductId = p.ProductId
            ORDER BY p.ModelId
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS ModelNames
FROM products p

answered Nov 05 '14 at 06:59

Scoregraphic

7,110
4
42
64

it duplicates the comma separated values - eg 12345 - win,xia,vor,win,xia,vor – sukesh Nov 05 '14 at 11:18
I tested your given setup on my SQL Server 2014 and things worked like expected. Is your real setup working slightly different? – Scoregraphic Nov 05 '14 at 14:09

score 1 · Answer 2 · answered Nov 05 '14 at 07:11

1

Try this:

SELECT DISTINCT
    p.ProductId,
    STUFF
    (
        (
            SELECT ',' + Name
            FROM models m
            WHERE m.ModelId IN (SELECT ModelId FROM products WHERE ProductId = p.ProductId)
            ORDER BY Name
            FOR XML PATH(''), type
        ).value('.', 'varchar(max)'), 1, 1, ''
    ) AS ModelNames
FROM
    products p

answered Nov 05 '14 at 07:11

Giorgos Betsos

71,379
9
63
98

Its still the same, as the o/p i was geeting – sukesh Nov 05 '14 at 07:36
@Qwerty I have tested my code in SSMS and it gives me these records as output: (12345 vor,win,xia), (12346 win,xia). I really can't think what could be going wrong with your configuration. – Giorgos Betsos Nov 05 '14 at 07:41

score 1 · Answer 3 · answered Nov 05 '14 at 07:36

try with this, this matches the output you are looking for

DECLARE @ProductModel AS Table(ProductId  INT,ModelId Varchar(10))

INSERT INTO @ProductModel
VALUES(12345,'A3666'), 
(12345,'A3667'),      
(12345,'A8999'),
(12346,'A3666'),
(12346,'A3667')

DECLARE @Model AS Table(ModelId  Varchar(10),Name Varchar(10))

Insert into @Model Values('A3666','win'),('A3667','xia'),('A8999','vor')          


SELECT
    p2.ProductId,
    STUFF
    (
        (
            SELECT ',' + Name
            FROM @ProductModel p1
            INNER JOIN @Model m ON m.ModelId=p1.ModelId
            WHERE p1.ProductId=p2.ProductId           
            ORDER BY Name
            FOR XML PATH(''), type
        ).value('.', 'varchar(max)'), 1, 1, ''
    ) AS ModelNames
FROM
    @ProductModel p2
    GROUP BY p2.ProductId

Hardik Parmar · Answer 4 · 2014-11-05T07:02:30.073

0

Please Replace this code with your stuff. I am really guessing this answer.

SELECT
    Distinct
    p.ProductId,
    coalesce(models + ', ', '')
FROM
    products p
    --Other joins----

edited Nov 05 '14 at 07:02

answered Nov 05 '14 at 06:53

Hardik Parmar

1,053
3
15
39

did not work. it gives all names for each product now. – sukesh Nov 05 '14 at 06:58

Get comma separated list of values - SQL

4 Answers4