x

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
more ▼

asked Jun 21, 2012 at 10:11 PM in Default

munabhai gravatar image

munabhai
0 4 4 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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!
more ▼

answered Jun 22, 2012 at 01:11 AM

JohnM gravatar image

JohnM
6.9k 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, 2012 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, 2012 at 03:11 PM JohnM
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Jun 22, 2012 at 04:31 PM

munabhai gravatar image

munabhai
0 4 4 4

(comments are locked)
10|1200 characters needed characters left
Thank you all
more ▼

answered Jun 22, 2012 at 04:31 PM

munabhai gravatar image

munabhai
0 4 4 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x48

asked: Jun 21, 2012 at 10:11 PM

Seen: 642 times

Last Updated: Jun 22, 2012 at 04:31 PM