[SOLVED] Stuff() Not Grouping Accurately

Stuff() Not Grouping Accurately

I am using an older version of SQL Server and trying to convert rows to concatenated columns. From researching here on stack overflow I see that I should be using STUFF(). However, when I attempt to replicate the answers I found here, I can't get the grouping correct. Instead of concatenating names tied to my GROUP BY, it's concatenating every single row and then just duplicating the results for every single row.

My base table #Temp is laid out as such:

CleanName	FullName	Total
Doe, Jane	DO, JANE	4
Doe, Jane	DOE, JANE S.	15
Doe, Jane	Doe, J.	23
Smith, John	Smith, J.	4
Smith, John	Smith, Jon	10
Smith, John	Smith, John	103

I am trying to get results like this:

CleanName	Concat_FullName	Sum(Total)
Doe, Jane	DO, JANE; DOE, JANE S.; Doe, J.	42
Smith, John	Smith, J.; Smith, Jon; Smith, John	117

This is what I tried running based on my research on stack overflow:

SELECT 
STAND_PRESC_NAME,
CONCAT_FULLNAME = STUFF(( SELECT '; ' + FULLNAME
            FROM #TEMP
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,''), 
SUM(TOTAL)
FROM #TEMP
GROUP BY STAND_PRESC_NAME

However what resulted was every row concatenated together which is not the desired results:

CleanName	Concat_FullName	Sum(Total)
Doe, Jane	DO, JANE; DOE, JANE S.; Doe, J.; Smith, J.; Smith, Jon; Smith, John	42
Smith, John	DO, JANE; DOE, JANE S.; Doe, J.; Smith, J.; Smith, Jon; Smith, John	117

How do I need to alter my STUFF() usage to appropriately group by CleanName?

Solution

You forgot to add the correlation:

SELECT 
STAND_PRESC_NAME,
CONCAT_FULLNAME = STUFF(( SELECT '; ' + FULLNAME
            FROM #TEMP t
            WHERE t.STAND_PRESC_NAME = t2.STAND_PRESC_NAME -- this
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,''), 
SUM(TOTAL)
FROM #TEMP t2
GROUP BY STAND_PRESC_NAME