How to combine multiple rows into one column?

A few days ago, I found a very smart usage of both STUFF and FOR XML PATH clauses, in order to combine multiple rows in one column. Just to remember it and to share the info, I wrote a small sample.

Not very well known, the STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

On the other hand, the new PATH mode allows you to use an XPath-like syntax as a column name which then is mapped into an attribute or element or sub element of the XML as per you specify and you can have multiple level of hierarchy and can mix elements n attributes.

Not clear? Let's take a look at a few lines of codes...

First, we are going to create a small test table:

IF object_id('tempdb..#SampleData') IS NOT NULL
BEGIN
DROP TABLE #SampleData
END

SELECT ID, NAME
INTO #SampleData
FROM (SELECT 91, 'Tupolev'
      UNION ALL SELECT 71, 'Embraer'
      UNION ALL SELECT 18, 'Boeing'
      UNION ALL SELECT 18, 'Lockheed-Martin'
      UNION ALL SELECT 25, 'Dassault'
      UNION ALL SELECT 25, 'Airbus') a(ID,NAME)

The magic happens here:

SELECT ID, STUFF((SELECT ', ' + NAME
          FROM #SampleData r2
          WHERE r2.ID = r1.ID
          ORDER BY ID
          FOR XML PATH('')), 1, 1, '') AS NAME
FROM #SampleData r1
GROUP BY ID

...and the long awaited result:

ID NAME
-- -----------------------
91 Tupolev
71 Embraer
18 Boeing, Lockheed-Martin
25 Dassault, Airbus

Cool, isn't it?