Merge Data from rows

Question

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

Answer 1

0

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!

answered Jun 22 '12 at 01:11 AM

JohnM
4.5k ● 1 ● 3 ● 7

+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

Jun 22 '12 at 05:49 AM Pavel Pawlowski

+1 @Pavel Pawlowski. Excellent suggestion. I didn't think about countries with a space in the name. Duh!

Jun 22 '12 at 03:11 PM JohnM

(comments are locked)

Answer 2

0

answered Jun 22 '12 at 04:31 PM

munabhai
0 ● 1 ● 1 ● 3

(comments are locked)

Answer 3

0

Thank you all

answered Jun 22 '12 at 04:31 PM

munabhai
0 ● 1 ● 1 ● 3

(comments are locked)

Merge Data from rows

Follow this question

Related Questions