July 6, 2012 at 3:06 pm
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?
July 6, 2012 at 3:11 pm
Please don't start a new thread with the same content as your other one. Please direct all replies to the original thread here. http://qa.sqlservercentral.com/Forums/Topic1319707-391-1.aspx
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply