I need to write a sql query on the table such that the result would have the group by column along with the aggregated column with comma separators.
My table would be in the below format
|`````````|````````|
| ID | Value |
|_________|________|
| 1 | a |
|_________|________|
| 1 | b |
|_________|________|
| 2 | c |
|_________|________|
Expected result should be in the below format
|`````````|````````|
| ID | Value |
|_________|________|
| 1 | a,b |
|_________|________|
| 2 | c |
|_________|________|
You want to use
FOR XML PATH
construct:The
STUFF
function is to get rid of the leading', '
.You can also see another examples here:
Just for a balanced view, you can also do this with a CTE but its not as good as the cross apply method I don't think. I've coded this of the hoof so apologies if it doesn't work.