Flattening SQL server INNER JOIN result

Question

Possible Duplicates:
How to return multiple values in one column (T-SQL)?
Simulating group_concat MySQL function in MS SQL Server 2005?

Hi,

Suppose I have 2 tables which i join in which i do a INNER JOIN using a key field.

Schema:

TABLE #a kf int

TABLE #b kf int, data varchar(5)

Now if #a has a single row with value as 1 and #b has multiple rows with the same key 1 as

1. 1 DBD
2. 1 DBE
3. 1 HDG

Now when i do a join instead of getting 3 rows , Is it possible to get one row with the data values in comma separated manner like

1 DBD,DBE,HDG

Answer 1

Here is a version that use a CTE and XML PATH('')

;with cte as
(
  select
    kf,
    (select data+','
     from #b as b2
     where b1.kf = b2.kf
     for xml path('')) as data
  from #b b1 
  group by kf
)
select
  a.kf, 
  left(b.data, len(b.data)-1) as data
from #a as a
  inner join cte as b
    on a.kf = b.kf