Joining tables and get column with comma seperated values out of the join result

[nainakarri]

Hi I have 2 tables table1 and table2 table1 has primary key column a which is joined with table2 column foriegnkey column b. on joining the two tables i get the result as below table1.a table2.b table2.c 1 1 x 1 1 y 1 1 z but i want the output as below table1.a table2.b table2.c 1 1 x,y,z can anyone help me how i can do this? want to avoid duplicates Regards Usha

Naina

[J4amieC]

Although probably possible, there is no easy way to do what you want - and it runs counter to the way database queries should work. You'd be far better off pulling your data out in the original way you posted and handling any weird aggregation of strings in code. FWIW here's your test code

create table #myTable (a INT, b INT, c VARCHAR(10))

INSERT INTO #myTable VALUES(1,1,'x')
INSERT INTO #myTable VALUES(1,1,'y')
INSERT INTO #myTable VALUES(1,1,'z')

INSERT INTO #myTable VALUES(2,2,'j')
INSERT INTO #myTable VALUES(2,2,'k')
INSERT INTO #myTable VALUES(2,2,'l')

declare @list VARCHAR(MAX), @a INT, @b INT
SELECT @a=1,@b=1

select @list = coalesce(@list + ',' + c ,c) from #myTable where a=@a AND b=@b
select a,b,@list as c
FROM #myTable
WHERE a=@a AND b=@b
group by a,b

drop table #myTable

[i j russell]

If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data.

[Mycroft Holmes]

i.j.russell wrote:

If you are using Sql Server 2005 or 2008 you can use FOR XML PATH to concatenate data

Do you have a link or an article for this or should I chase through BOL

Never underestimate the power of human stupidity RAH

[i j russell]

http://www.mahipalreddy.com/blog/?p=79[^]

[Mycroft Holmes]

Thanks i.j

Never underestimate the power of human stupidity RAH

[Niladri_Biswas]

Try this declare @pk_table table (id int) insert into @pk_table select 1 union all select 2 union all select 3 union all select 4 declare @fk_table table (id int, string varchar(2)) insert into @fk_table select 1,'x' union all select 1,'y' union all select 1,'z' union all select 2,'a' union all select 2,'b' union all select 2,'c' union all select 3,'d' union all select 3,'e' union all select 3,'f' union all select 4,'g' union all select 4,'h' union all select 4,'i'

select cast(id as varchar(20)) + space(2) + cast(id as varchar(20)) + space(2) +
(select string + ','
from @fk_table fkt
where fkt.id = pkt.id
FOR xml path('')) as OutputCol
from @pk_table pkt
group by pkt.id

:)

Niladri Biswas

[nainakarri]

Hi thanks for all the replies. I finally approached by calling a function from select query. Select col1, col2, func() as col3,col4 from table1 a inner join table2 b on a.col1 = b.col2 Regards Naina

Naina

[Niladri_Biswas]

Hello, I suggestion.. if possible try to avoid using function as it causes performance bottleneck. :)

Niladri Biswas