Sql 2005 join results

sqlsql-server-2005

HI,

Using Microsoft SQL Server 2005:

I have a table "test":

id, link_id, name

1 11 test1

2 11 test2

3 11 test3

4 12 test4

Is there a way to query this, and return the results grouped by "link_id", with the names joined?

EG,

SELECT link_id, name FROM test WHERE ???????

results:

link_id, name(s)

11 test1, test2, test3

12 test4

Best Answer

The blackbox way of doing this is with a CROSS APPLY and FOR XML PATH:

declare @t table (id int, link_id int, name varchar(max))
insert into @t select 1, 11, 'test1'
union all select 2, 11, 'test2'
union all select 3, 11, 'test3'
union all select 4, 12, 'test4'

select b.link_id, d.link_names
from (
    select distinct link_id
    from @t a
) b
cross apply (
    select name + ', ' as [text()]
    from @t c
    where b.link_id = c.link_id
    for xml path('')
) d (link_names)

For each row, a CROSS APPLY executes the applied subquery. In this case, the subquery is called twice, for link_id 11 and 12. The subquery then abuses the FOR XML operator to add the strings together.

If you run the query, it will print:

11   test1, test2, test3, 
12   test4,
Related Topic