SQL Server : concat each individual columns into 1 row

Question

I have a pretty complex query that returns information like this:

I would like to group all columns like this:

Is there a simple way to do it?

Answer 1

This would be one way of getting your result:

SELECT 
   t.ID,
   STUFF((SELECT distinct ', ' + t.InternalRef
          FROM #t t
          FOR XML PATH('')), 1, 1, '') [InternalRef],

   STUFF((SELECT distinct ', ' + t.ExternalRef
   FROM #t t
   FOR XML PATH('')), 1, 1, '') [ExternalRef],

   STUFF((SELECT distinct ', ' + t.ComposantIdentifier
   FROM #t t
   FOR XML PATH('')), 1, 1, '') [ComposantIdentifier]
FROM #t t
GROUP BY t.ID
ORDER BY 1

You can try a demo of this here.

Answer 2

Here you go:

WITH cte AS (
    SELECT 368519 AS Id, '243625378R' AS InternalRef, '492-90-113' AS ExternalRef, '1749MA' AS ComponentIdentifier
    UNION ALL
    SELECT 368519 AS Id, '243625378R' AS InternalRef, '492-90-114' AS ExternalRef, '1749MA' AS ComponentIdentifier
    UNION ALL
    SELECT 368519 AS Id, '(8200945715)' AS InternalRef, '(7807965)' AS ExternalRef, '1749MA' AS ComponentIdentifier)
SELECT
    Id,
    STUFF((SELECT ',' + InternalRef FROM cte FOR XML PATH ('')), 1, 1, ''),
    STUFF((SELECT ',' + ExternalRef FROM cte FOR XML PATH ('')), 1, 1, ''),
    ComponentIdentifier
FROM
    cte
GROUP BY
    Id,
    ComponentIdentifier;

The assumption is that cte, the common-table expression is the output from your existing query. So you could wrap this up in a common-table expression (replacing my UNION statement), put it into a temporary table, a table variable, etc.

I'm also making some assumptions that might be wrong, but my output matches yours:

Id      (No column name)                    (No column name)        ComponentIdentifier
368519  243625378R,243625378R,(8200945715)  492-90-113,492-90-114,(7807965) 1749MA