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