Converting multiple rows to comma delimited value-CodePudding

I am using SQL Server 2014.

This is my current query. I have tried to group by id and category and use the stuff function to concat identifictaion_number but still can't get the right result. Let me know if additional information is needed.

SELECT
    t1.id,  t2.identification_number, t3.description AS 'category'
FROM 
    table1 t1
JOIN 
    table2 t2 ON t2.wuID = t1.id
JOIN 
    lookupTable3 t3 ON t3.id = t2.itID

Current output:

id   identification_number  category
-----------------------------------
100  123                    cat1
100  345                    cat2
100  567                    cat2

Desired output:

id   identification_number  category
-----------------------------------
100  123                    cat1
100  345, 567               cat2

CodePudding user response：

Using STRING_AGG we can try:

SELECT
    t1.id,
    STRING_AGG(t2.identifiction_number, ', ')
        WITHIN GROUP (ORDER BY t2.identifiction_number) AS identifiction_number,
    t3.description AS category
FROM table1 t1
INNER JOIN table2 t2 ON t2.wuID = t1.id
INNER JOIN lookupTable3 t3 ON t3.id = t2.itID
GROUP BY
    t1.id,
    t3.description;

CodePudding user response：

what are you looking is called String_agg and it is available since 2017. It act as sum for string values. your final query

SELECT t1.id,
       String_agg(t2.identification_number, ',') AS identification_number,
       t3.description                            AS 'category'
FROM   table1 t1
       JOIN table2 t2
         ON t2.wuid = t1.id
       JOIN lookuptable3 t3
         ON t3.id = t2.itid
GROUP  BY t1.id,
          t3.description

CodePudding user response：

Here it what it would look like with a stuff statement, if you are interested :

    SELECT 
    distinct t1.ID,
    t5.description,
    STUFF((Select ', '  t2.[identification_number]
        from
            table1  t1
            inner join Table2 t2 on T2.Wuid =T1.ID
            inner join lookupTable3 t3 on t3.ID =t2.itID and t3.description=t5.description
        FOR XML PATH('')),1,2,'') [identification_number] 
FROM 
    table1 t1
    inner join Table2 t4 on T4.Wuid =t1.ID
    inner join lookupTable3 t5 on t5.ID =t4.itID