Issue
Using SQL 2008 R2 I have two tables and want to create a table like:
Input tables input table 1
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.
Solution
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
Answered By - cpalmer Answer Checked By - Mildred Charles (PHPFixing Admin)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.