Merge Data

Question

Post reply

Merge Data

Munabhai

Default port

Points: 1445
More actions
June 21, 2012 at 4:10 pm

#258360

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
1China, Germmany, USA
2China
3India, USA
4China, USA, India, Germmany

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Easiest way is with a FOR XML trick.

Check out this article:

http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here is some code to play with as well.

create table dbo.EmployeeWork

(

empid int,

CountryWorkAt Varchar(20)

);

insert into dbo.EmployeeWork values (1,'China');

insert into dbo.EmployeeWork values (1,'Germany');

insert into dbo.EmployeeWork values(1,'USA');

insert into dbo.EmployeeWork values(2,'China');

insert into dbo.EmployeeWork values(3,'India');

insert into dbo.EmployeeWork values(3,'USA');

insert into dbo.EmployeeWork values(4,'China');

insert into dbo.EmployeeWork values(4,'USA');

insert into dbo.EmployeeWork values(4,'India');

insert into dbo.EmployeeWork values(4,'Germany');

go

with basedata as (

select distinct empid from dbo.EmployeeWork

)

select

bd.empid,

STUFF((select ',' + ew.CountryWorkAt

from dbo.EmployeeWork ew

where ew.empid = bd.empid

for xml path(''), type).value('.','varchar(max)'),1,1,'') AS Works

from

basedata bd;

go

drop table dbo.EmployeeWork;

go

Munabhai Default port Points: 1445 More actions · Answer 3

Munabhai

Default port

Points: 1445

June 21, 2012 at 4:35 pm

#1504292

Thanks

Munabhai Default port Points: 1445 More actions · Answer 4

Hi SSCoach, it does works great, Instead of "," if I want to put <p> how do I do that? I try all the position but it display at first

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Munabhai (6/21/2012)
Hi SSCoach, it does works great, Instead of "," if I want to put <p> how do I do that? I try all the position but it display at first

Not sure what you are looking for here.

Munabhai Default port Points: 1445 More actions · Answer 6

create table dbo.EmployeeWork

(

empid int,

CountryWorkAt Varchar(20)

);

insert into dbo.EmployeeWork values (1,'China');

insert into dbo.EmployeeWork values (1,'Germany');

insert into dbo.EmployeeWork values(1,'USA');

insert into dbo.EmployeeWork values(2,'China');

insert into dbo.EmployeeWork values(3,'India');

insert into dbo.EmployeeWork values(3,'USA');

insert into dbo.EmployeeWork values(4,'China');

insert into dbo.EmployeeWork values(4,'USA');

insert into dbo.EmployeeWork values(4,'India');

insert into dbo.EmployeeWork values(4,'Germany');

go

--output

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

with basedata as (

select distinct empid from dbo.EmployeeWork

)

select

bd.empid,

STUFF((select ',' + ew.CountryWorkAt

from dbo.EmployeeWork ew

where ew.empid = bd.empid

for xml path(''), type).value('.','varchar(max)'),1,1,'') AS Works

from

basedata bd;

go

Above code work grate in Sql Server 2008. But I have to work in SQL Server 2000,

could you please help me?

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Well you did post this in the sql 2008 forum. 😀 This type of formatting really should be left to the front end to deal with. It is not that easy to force sql to do this, especially in sql 2000.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

This post from MS shows a way to do this with a UDF in 2000. From your sample data this should do exactly what you are looking for. Just make sure you scroll down a little ways to the 2000 solution.

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I'll have to defer on modifying this to work on SQL Server 2000 to someone who still works with SQL Server 2000. I haven't touched a SQL Server 2000 system in about 6 or 7 years.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Lynn Pettis (7/6/2012)
I'll have to defer on modifying this to work on SQL Server 2000 to someone who still works with SQL Server 2000. I haven't touched a SQL Server 2000 system in about 6 or 7 years.

Actually Lynn that article I dug up has a pretty straight forward way to do it with a UDF...not what I would do on a newer DB but it looks pretty simple.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Sean Lange (7/6/2012)
Lynn Pettis (7/6/2012)
I'll have to defer on modifying this to work on SQL Server 2000 to someone who still works with SQL Server 2000. I haven't touched a SQL Server 2000 system in about 6 or 7 years.
Actually Lynn that article I dug up has a pretty straight forward way to do it with a UDF...not what I would do on a newer DB but it looks pretty simple.

Well, I gave it a shot before replying, but nothing simple that I tried worked, and I wasn't going to work too hard for a SQL Server 2000 solution when I don't even support any SQL Server 2000 installations.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Me neither, plus I figured it had been done before so I just used a little Googlefu. The solution I found was a scalar udf but hey, it is sql 2000 so you get what you get with that one.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Munabhai Default port Points: 1445 More actions · Answer 13

Thank you Sean Lange. I will try using user define function.

Munabhai Default port Points: 1445 More actions · Answer 14

Sir that was sample of table, it not real table. If I solve this problem, then I can easily do my real problem.