May 14, 2004 at 2:34 am
Hi, All:
Here is a question that I could not figure out. Please share your idea if you know the answer...
I have a table:
id col1 col2
--- ----- -----
1 A kk
1 B jj
1 C bb
2 D qq
2 E ff
The following should be the result:
id col1 col2
--- ------- --------
1 A,B,C kk,jj,bb
2 D,E qq,ff
I know it surely can be done writing a stored procedure. Is it possible to get the result writing a SQL command, not a stored procedure? I couldn't find a function that has the function of concat...
Thank you for your help!~
May 14, 2004 at 6:08 am
Test code:
create table
(id int, col1 char(1), col2 char(2))
insert
select 1, 'A', 'kk'
insert
select 1, 'B', 'jj'
insert
select 1, 'C', 'bb'
insert
select 2, 'D', 'qq'
insert
select 2, 'E', 'ff'
go
alter function fncolcsv(@id int, @colpos int)
returns varchar(100)
as
begin
declare @return varchar(100)
set @return=''
if @colpos = 1
begin
select @return = @return + col1+',' from
where id = @id
set @return = left(@return, len(@return)-1)
end
else
begin
select @return = @return + col2+',' from
where id = @id
set @return = left(@return, len(@return)-1)
end
return @return
end
go
select distinct id, dbo.fncolcsv(id,1) as col1, dbo.fncolcsv(id,2) as col2 from
go
drop function fncolcsv
drop table
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply