asked
Merge Data from rows
Create table EmployeeWork ( empid int, CountryWorkAt Varchar(20) ) insert into EmployeeWork values (1,'China') insert into EmployeeWork values (1,'Germany') insert into EmployeeWork values(1,'USA') insert into EmployeeWork values(2,'China') insert into EmployeeWork values(3,'India') insert into EmployeeWork values(3,'USA') insert into EmployeeWork values(4,'China') insert into EmployeeWork values(4,'USA') insert into EmployeeWork values(4,'India') insert into EmployeeWork values(4,'Germany') Merge each row base on empid Output should look like this 1 China, Germmany, USA 2 China 3 India, USA 4 China, USA, India, Germmany
Would this work for you? SELECT DISTINCT empid, CountryWorkAt = REPLACE( ( SELECT CountryWorkAt AS [data()] FROM EmployeeWork ew WHERE ew.empID = e.empID ORDER BY empID FOR XML PATH ('') ), ' ', ',') FROM EmployeeWork e ORDER BY empID Solution modified from
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html Hope this helps!
+1 @John Morehouse, I only suggest to modify the query to use **`STUFF`** instead of `REPLACE` because in case you will have a country name sosisting from more than one word, then the words will be separated by comma in final output, as REPLACE will replace all the occurences of space. SELECT empid, CountryWorkAt = STUFF( (SELECT ',' + CountryWorkAt FROM EmployeeWork we WHERE we.empID = e.empID FOR XML PATH ('')), 1, 1, '') FROM EmployeeWork e GROUP BY empid
Thank you all
10 People are following this question.
