Concatenate fields from a joined query

Question

I need to update a field with concatenated results from a T-SQL query that uses an INNER JOIN and a LEFT JOIN. I was able to do this with the STUFF and FOR XML PATH functions with a simpler query, but my efforts at doing the same process with a more elaborate query have not been successful.

Here is the query that gives me the results I need with the ID field going to end up as the grouping and the Step field will be the one where the concatenated values need to be in the one field per one ID.

SELECT sc.ID, sc.STEP
FROM Table1 As sc
INNER JOIN Table2 As f
    ON sc.STEP = f.Step AND sc.STEP_TYPE = f.StepType AND   
sc.OldStep = f.OldStep
LEFT JOIN Table3 As l
ON sc.ID = l.ID
WHERE f.Group = l.Group AND sc.CompDate IS NULL

That will give me my results broken down into multiple fields per ID

•ID-----STEP

• 01 - 101
• 01 - 102
• 01 - 103
• 02 - 107
• 02 - 113

And what I need is:

•ID-----STEP

• 01 - 101, 102, 103
• 02 - 107, 113

Here is what i've tried so far:

;With OA As 
(   SELECT s.ID, STUFF((
    SELECT ', ' + sc.STEP
    FROM Table1 As sc
    WHERE sc.ID = s.ID
    ORDER BY sc.ID
    FOR XML PATH('')),1,1,'') As Steps
FROM Table1 As s
INNER JOIN Table2 As f
    ON s.STEP = f.Step AND s.STEP_TYPE = f.StepType 
            AND s.OldStep = f.OldStep
LEFT JOIN Table3 As l
    ON s.ID = l.ID
WHERE f.Group = l.Group AND s.CompDate IS NULL
GROUP BY s.ID
)

SELECT * FROM OpenAuditSteps

The problem here is that I am getting a concatenation of all the reocrds, not just the ones grouped on the individual ID's. I've tried various ways of arranging the joins, but nothing has worked so far.

Answer 1

You are very nearly there: You already have your first query working. Assuming the results of that go into #Table1 then

SELECT  Distinct
        sc1.ID,
    STUFF (( Select  ',' + sc2.STEP 
    FROM #Table1 AS SC2
    WHERE sc2.ID = sc1.ID
    FOR XML PATH('')),1,1,'') AS STEPS
FROM #Table1 AS SC1
ORDER BY sc1.ID

So to combine it into one single query using WITH try this:

    ;WITH IDSteps AS (
SELECT sc.ID, sc.STEP
FROM Table1 As sc
INNER JOIN Table2 As f
    ON sc.STEP = f.Step AND sc.STEP_TYPE = f.StepType AND   
sc.OldStep = f.OldStep
LEFT JOIN Table3 As l
ON sc.ID = l.ID
WHERE f.Group = l.Group AND sc.CompDate IS NULL
)
SELECT  Distinct
        sc1.ID,
    STUFF (( Select  ',' + sc2.STEP 
    FROM IDSteps AS SC2
    WHERE sc2.ID = sc1.ID
    FOR XML PATH('')),1,1,'') AS STEPS
FROM IDSteps AS SC1
ORDER BY sc1.ID;