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

avatar 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

avatar image

JohnM
12.4k 3 7 14

  • @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
  • @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

avatar 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

avatar 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.

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:

x53

asked: Jun 21, 2012 at 10:11 PM

Seen: 768 times

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

Copyright 2016 Redgate Software. Privacy Policy