Connect together different values with splitter that have been grouped by

Question

In picture result is what result of that UNION ALL inner select looks like.

I'm having troubles connecting together values assigned to same ID. In inner select I can get multiple same ID results, but always with different ValueField value. I need to group that results by inn.ID and I need to connect all different ValueField into 1 column. Different values would be split by ','. You don't need to worry about UNION ALL result, that is correct and I need to work with that result

So if my select returns

ID: xxx FieldValue: FV1 
ID: xxx FieldValue: FV55 
ID: xxx FieldValue: FV66 
ID: 123 FieldValue: FV789

Then final result would be:

ID: xxx FieldValue: FV1, FV55, FV66
ID: 123 FieldValue: FV789

This is my current code:

SELECT 
    inn.ID 
    --STUFF GROUPPED RESULTS(ValueField)

FROM
(
    SELECT te2.Root_ID AS ID, te1.ValueField 
    FROM TableExample2 AS te2
    LEFT JOIN TableExample1 te1 ON te2.te1ID= te1.ID
    WHERE te1.Deleted = 0
    GROUP BY te2.Root_ID, te1.ValueField

    UNION ALL

    SELECT ID, ValueField          
    FROM TableExample1 AS main
    WHERE ID IS NOT NULL

) AS inn

GROUP BY inn.ID

I would like to use GROUP_CONCAT, but my SQL Server doesn't support that. I tried with STUFF(), but my results aren't still grouped and connected.

Thank you very much for help. I appriciate it

Answer 1

Here's how you can modify your query using a CTE:

;WITH inn AS (
    SELECT te2.Root_ID AS ID, te1.ValueField 
    FROM TableExample2 AS te2
    LEFT JOIN TableExample1 te1 ON te2.te1ID = te1.ID
    WHERE te1.Deleted = 0
    GROUP BY te2.Root_ID, te1.ValueField
    UNION ALL
    SELECT ID, ValueField          
    FROM TableExample1 AS main
    WHERE ID IS NOT NULL
)
SELECT 
    ID,
    STUFF((SELECT ', ' + CAST(inn2.ValueField AS VARCHAR(MAX))
           FROM inn AS inn2
           WHERE inn2.ID = inn.ID
           FOR XML PATH('')), 1, 2, '') AS GroupedFieldValues
FROM inn
GROUP BY ID;