007 007 - 7 months ago 16
SQL Question

T-SQL- Concatenate variable # of rows

Code:

DECLARE @GroupByCnt INT = 3;

CREATE TABLE #Temp (ID VARCHAR(15))

INSERT INTO #Temp
SELECT '123'
UNION ALL SELECT '123'
UNION ALL SELECT '234'
UNION ALL SELECT '345'
UNION ALL SELECT '456'
UNION ALL SELECT '456'
UNION ALL SELECT '456'
UNION ALL SELECT '567'
UNION ALL SELECT '678'
UNION ALL SELECT '789'
UNION ALL SELECT '890'
UNION ALL SELECT '901'
UNION ALL SELECT '901'

SELECT DISTINCT STUFF((
SELECT ',' + ID
FROM #Temp T1
WHERE T1.ID = T2.ID
FOR XML PATH('')
), 1, 1, '') ID
FROM #Temp T2

DROP TABLE #Temp


Current output:

ID
123,123
234
345
456,456,456
567
678
789
890
901,901


Desired output:

ID
123,234,345
456,567,678
789,890,901


Goal:

I want to concatenate a set # of unique records (comma separated).

So in the above example, I want to concatenate 3 unique records at a time (and not altogether). There're 9 unique records; so because I want them grouped by 3, I would end up with 3 total records with 3 records worth of concatenation in each one of these output record (order by ID).

Answer

Looks like presentation matter only and it should be done in application layer.


You could use DENSE_RANK() and integer division to create groups:

WITH cte AS
(
   SELECT DISTINCT ID, (DENSE_RANK() OVER(ORDER BY ID) - 1)/3 AS grp 
   FROM #Temp
)
SELECT DISTINCT STUFF((SELECT ',' + ID
                       FROM cte T1
                       WHERE T1.grp = T2.grp
                       ORDER BY ID
                       FOR XML PATH('')
                       ), 1, 1, '') ID
FROM cte T2;

LiveDemo

Output:

╔═════════════╗
║     ID      ║
╠═════════════╣
║ 123,234,345 ║
║ 456,567,678 ║
║ 789,890,901 ║
╚═════════════╝
Comments