Converting multiple rows to comma delimited value

Question

I am using SQL Server 2014. I have made a a sqlFiddle 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
101  9899                   cat2

Desired output:

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

https://sqlblog.org/2022/02/17/string-aggregation-over-the-years-in-sql-server — Aaron Bertrand, Mar 02 '22 at 05:14

RF1991 · Answer 1 · 2022-03-02T05:19:42.143

3

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

edited Mar 02 '22 at 05:19

answered Mar 02 '22 at 05:13

RF1991

2,037
4
8
17

I am using version 2014 where it seems String_agg is not available – nogewe5511 Mar 02 '22 at 05:51
use subqury and concat() in your case in addition stuff function – RF1991 Mar 02 '22 at 07:58

score 3 · Answer 2 · answered Mar 02 '22 at 05:14

3

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;

answered Mar 02 '22 at 05:14

Tim Biegeleisen

502,043
27
286
360

I am using version 2014 where it seems String_agg is not available – nogewe5511 Mar 02 '22 at 05:51
...kind of helps to reveal this information in your question. – Tim Biegeleisen Mar 02 '22 at 06:03

Tony · Answer 3 · 2022-03-02T17:32:30.940

1

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  t11
            inner join Table2 t2 on T2.Wuid =T11.ID
            inner join lookupTable3 t3 on t3.ID =t2.itID and t3.description=t5.description
            where t1.id = t11.id
        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

edited Mar 02 '22 at 17:32

answered Mar 02 '22 at 05:28

Tony

109
3

FYI the `FOR XML PATH` is what is doing all the work here, building a comma-separated list of the relevant values. `STUFF` merely removes the leading comma. – Aaron Bertrand Mar 02 '22 at 12:17
@Tony This seems to also be adding ids for other types. Please see this [sqlFiddle](http://sqlfiddle.com/#!18/da636/1). The first row is correct. The second row should only return `456,789`. And the third row should only return `9899` – nogewe5511 Mar 02 '22 at 15:32
You also need to unescape XML, see https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – Charlieface Mar 02 '22 at 15:58
@Tony I am editing your code that gives me the right output. Let me know if there are any issues with it. – nogewe5511 Mar 02 '22 at 16:01
nvm apparently I can't edit your code – nogewe5511 Mar 02 '22 at 16:05
@Tony nvm apparently I can't edit your code. But basically, I changed the alias for table1 inside the stuff statement to `table1 t11` and then had a where statement inside the stuff statement as well. `where t11.id = t1.id`. Updated [fiddle](http://sqlfiddle.com/#!18/da636/22). This gave me the correct output. I initially tried grouping it by id and description as well but that didn't give the right output as well. I am not sure why the question got closed. Let me know if you see any problems with that change. – nogewe5511 Mar 02 '22 at 16:14
@nogewe5511 I edited the scripts to add the where clause in the statement. Thanks. – Tony Mar 02 '22 at 17:38

Converting multiple rows to comma delimited value

3 Answers3