GROUP multiple text fields from multiple rows into one column

Question

I have the following statement:

SELECT *
FROM 
    (
    SELECT 123 AS id, 123445 AS OrderNr, '775-1x' AS Reference
    UNION ALL
    SELECT 123 AS id, 123445 AS OrderNr, '775-1xx' AS Reference
    UNION ALL
    SELECT 123 AS id, 123445 AS OrderNr, '775-1xxx' AS Reference
    ) AS SUB

This is how the result should look like:

SELECT *
FROM 
    (
    SELECT 123 AS id, 123445 AS OrderNr, '775-1x, 775-1xx, 775-1xxx' AS Reference
    ) AS SUB

Please include how you want the result to appear. That will help ensure we understand your question. — Wes H, Jun 11 '18 at 13:42

score 1 · Accepted Answer · answered Jun 11 '18 at 13:41

You seems want xml method with stuff() function :

with sub as (
       SELECT 123 AS id, 123445 AS OrderNr, '775-1x' AS Reference
       UNION ALL
       SELECT 123 AS id, 123445 AS OrderNr, '775-1xx' AS Reference
       UNION ALL
       SELECT 123 AS id, 123445 AS OrderNr, '775-1xxx' AS Reference
)
select id, OrderNr,
       stuff( (select ','+Reference 
               from sub s1 
               where s1.id = s.id and s1.OrderNr = s.OrderNr
               for xml path('')
              ), 1, 1, ''
            ) as Reference
from sub s
group by id, OrderNr;

GROUP multiple text fields from multiple rows into one column

1 Answers1