Sql Grouping insight Xml

I need to get different row data to 1 row based with id and need to group by its status.

I tried in several ways and finally found a solution using xml, but when I use xml I don’t get the output I want.

This is a sample code I used to try the logic.

drop table  #TestTable
-- Create table
CREATE TABLE #TestTable (id VARCHAR(100), title VARCHAR(100), 
progress VARCHAR(100))
GO
-- Populate tableid
INSERT INTO #TestTable (id, title, progress)
SELECT '1', 'test1', 'inprogress'
UNION ALL
SELECT '1', 'test2', 'inprogress'
UNION ALL
SELECT '1', 'test3', 'completed'
UNION ALL
SELECT '1', 'test4', 'completed'

GO

SELECT id,progress, comments = STUFF((SELECT +' , '+ TITLE
    FROM #TestTable AS x2 WHERE ID = x.ID 
   
     ORDER BY ID
     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')
FROM #TestTable AS x 


GROUP BY id,progress

returned output:

id  progress      comments
1   completed   , test1 , test2 , test3 , test4
1   inprogress  , test1 , test2 , test3 , test4

expected output:

id  progress     comments
1   completed   , test1 , test2 
1   inprogress  , test3 , test4

Answer

I think you are missing a condition in your join (progress = x.progress):

SELECT id,progress, comments = STUFF((SELECT +' , '+ TITLE
    FROM #TestTable AS x2 WHERE ID = x.ID  
        and progress = x.progress  -- <-- add this 
        ORDER BY ID
     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')
FROM #TestTable AS x 
GROUP BY id,progress

Output:

Advertisement

Answer