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