sql server - SQL Multiple Rows to one CSV -
this question has answer here:
- concatenate many rows single text string? 38 answers
i'm having issues converting concatenating multiple rows 1 row csv
this have
[project number] | [resources] 1 25254 , jim anderson 2 25254 , becky smith 3 32564 , amy dickerson 4 32564 , james walsh 5 25679 , jim anderson 6 25679 , james walsh
this need
[project number] | [resources] 1 25254 jim anderson, becky smith 2 32564 amy dickerson, james walsh 3 25679 jim anderson, james walsh
this query i'm using
select * from( select 'project number' = p.projectid , 'resources' = ', ' + pe.name projects p (nolock) left join persons pe (nolock) on p.personsid = pe.personsid year( p.createddtm) = 2013 , p.typeid = 1 ) ta order ta.apaid
please help.
thank you.
select [project number], stuff((select ',' + cast(t2.resources varchar(10)) t t2 t1.[project number]= t2.[project number] xml path('')),1,1,'') resources t t1 group [project number]
output:
| project number | resources | |----------------|-----------------------| | 25254 | jim anders,becky smit | | 25679 | jim anders,james wals | | 32564 | amy dicker,james wals |
Comments
Post a Comment