I am on version 2012 and need to use the XLM path to get a common separated string for like projects. I have a temp table that I have compiled and would like to combine people based on project numbers. The results combine all people into one list for all projects. I am looking to get specific to project.
select #Team.WBS1,
stuff((
Select ',', #Team.Team as [text()]
from #Team
Where #team.WBS1 = #team.wbs1
for xml path('')
), 1, 1, '')
from #Team
I am getting the same results for every project number.
| Project | People |
|---|---|
| 1 | john, jim, sean, billy |
| 1 | john, jim, sean, billy |
| 1 | john, jim, sean, billy |
| 1 | john, jim, sean, billy |
| 2 | john, jim, sean, billy |
| 3 | john, jim, sean, billy |
| 3 | john, jim, sean, billy |
| 4 | john, jim, sean, billy |
| 4 | john, jim, sean, billy |
I need results like this
| Project | People |
|---|---|
| 1 | john, jim, sean, billy |
| 2 | billy |
| 3 | jim, sean, |
| 4 | john, jim, billy |
Data looks like this
| Project | People |
|---|---|
| 1 | john |
| 1 | jim |
| 1 | sean |
| 1 | billy |
| 2 | billy |
| 3 | jim |
| 3 | sean |
| 4 | john |
| 4 | jim, billy |
1