February 9, 2010 at 8:37 am
Hi all, I have the following problem when using sql server 2000
The following table has around 200 records.
Company / Employee
1005 / A
1005 / B
1005 / C
1010 / X
1010 / Y
1020 / L
1020 / M
etc etc
I wish to create the following (comma seperated) output:
Company / Employees
1005 / A, B, C
1010 / X. Y
1020 / L, M
etc etc
Im having a really hard time with this in sql server 2000. while 2005 seems to offer easier solutions to solve this issue! i hope someone has a bright idea, to explain how to solve this...
February 9, 2010 at 9:29 pm
mlandsheer (2/9/2010)
Im having a really hard time with this in sql server 2000. while 2005 seems to offer easier solutions to solve this issue! i hope someone has a bright idea, to explain how to solve this...
Hi,
To set this type of cross Pivoting is, may difficult in the sql2000.
One of the best options of this, first Concatenation of the column in the separate function and then call this function in the select table.
create table MYTABLE
(
company varchar(4),
employee varchar(2)
)
insert into MYTABLE
select '1005','A'
union all
select '1005','B'
union all
select '1005','C'
union all
select '1006','AB'
union all
select '1006','BC'
union all
select '1006','CA'
/*create function to concat of the column value*/
create function dbo.concate (@company varchar(4))
returns nvarchar(1000)
as
begin
declare @concat nvarchar(1000)
select @concat=coalesce(@concat,'')+employee+',' from MYTABLE where company =@company
select @concat = substring(@concat,1,len(@concat)-1)
return (@concat)
end
/*call the function in the select table*/
select company,(select dbo.concate (company))as result
from MYTABLE
group by company
February 10, 2010 at 2:24 am
this has been wonderful help! thank you so much!
February 17, 2010 at 3:15 pm
what a nice method.. saved me a cursor and additional coding..
thx man..
Cheers,
John Esraelo
April 22, 2010 at 11:45 am
Check this approach... Simple and effective as well.
SELECT
S.ServiceId,
STUFF(
(
SELECT ',' + [Description]
FROM ServiceServiceFunctions SSF
INNER JOIN ServiceFunctions SF
ON SF.ServiceFunctionId = SSF.ServiceFunctionId
WHERE SF.ServiceFunctionId = SSF.ServiceFunctionId
AND SSF.Serviceid = S.Serviceid
FOR XML PATH('')
), 1, 1, '') as functions
FROM Services
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply