I was wondering if it was possible to use the PIVOT function in SQL Server to pivot data, but instead of into separate columns, I want to have all pivoted values in one column that is comma separated. The data would be pivoted/grouped based on an ID column. Is there a way to do this dynamically for each ID? The real data set I'm working with has thousands of different IDs.
Here is an example of what I am attempting to do:
I want to turn this:
| ID | Document Number |
|---|---|
| 1 | 10 |
| 1 | 20 |
| 1 | 30 |
| 2 | 10 |
| 2 | 50 |
| 2 | 60 |
into this:
| ID | Document |
|---|---|
| 1 | 10,20,30 |
| 2 | 10,50,60 |
