how to concatenate multiple rows into one

Question

Using SQL 2008 R2 I have two tables and want to create a table like:

output

Input tables input table 1

inout table 2

I have tried using stuff function as

SELECT o.DEPT_ID,o.CLIENT_ID,
       code,
       (STUFF((SELECT CAST(', ' + CODE AS VARCHAR(MAX)) 
         FROM ORDERS
         WHERE (o.FUNDER_ID = f.FUNDER_ID) 
         FOR XML PATH ('')), 1, 2, '')) AS funder_code
FROM FUNDERS f
join ORDERS o on o.FUNDER_ID=f.FUNDER_ID 
where o.DEPT_ID=111 and CLIENT_ID='B001'

and I'm not getting the output.

Answer 1

First of all, your desired output appears to have the 'name' column from your second input table as a comma-separated list, but your code implies that you want the 'code' column concatenated instead. This solution concatenates the 'name' column.

Second, looking at your input tables, you can't directly use join ORDERS o on o.FUNDER_ID=f.FUNDER_ID because 'B0000000019' does not equal 'F19'. However, once you manipulate those columns so they could be joined, try this:

SELECT DISTINCT o.dept_id, o.client_id
    ,(STUFF((SELECT distinct CAST(', ' + name AS VARCHAR(MAX)) 
         FROM FUNDERS f
         JOIN ORDERS o2 ON o2.funder_id = f.funder_id
         FOR XML PATH ('')), 1, 2, '')) AS funder_code
FROM ORDERS o