Get comma separated list of values - SQL [duplicate]

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.]

Answer 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

Answer 2

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

Answer 3

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

Answer 4

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

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