Here is how it works:
**1. Get XML element string with FOR XML**
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
By passing in a blank string (FOR XML PATH('')), we get the following instead:
,aaa,bbb,ccc,ddd,eee
**2. Remove leading comma with STUFF**
The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
The parameters of STUFF are:
The string to be “stuffed” (in our case the full list of name with a leading comma)
The location to start deleting and inserting characters (1, we’re stuffing into a blank string)
The number of characters to delete (1, being the leading comma)
So we end up with:
aaa,bbb,ccc,ddd,eee
**3. Join on id to get full list**
Next we just join this on the list of id in the temp table, to get a list of IDs with name:
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
And we have our result:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Hope this helps!
Here is how it works:
**1. Get XML element string with FOR XML**
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
By passing in a blank string (FOR XML PATH('')), we get the following instead:
,aaa,bbb,ccc,ddd,eee
**2. Remove leading comma with STUFF**
The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
The parameters of `STUFF` are:
- The string to be “stuffed” (in our case the full list of name with a
leading comma)
- The location to start deleting and inserting characters (1, we’re stuffing into a blank string)
- The number of characters to delete (1, being the leading comma)
So we end up with:
aaa,bbb,ccc,ddd,eee
**3. Join on id to get full list**
Next we just join this on the list of id in the temp table, to get a list of IDs with name:
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
And we have our result:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Hope this helps!
