Concatenate 5 rows at a time

How can I Concatenate 5 rows at a time.

ID
518
519
520
521
522
523
524
525
526
527

Desired results...

518,519,520,521,522
523,524,525,526,527
LVL 8
JRockFLAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
CREATE TABLE #Test
(
      ID      INT
)

INSERT INTO #Test (ID)
VALUES (518),
    (519),
      (520),
      (521),
      (522),
      (523),
      (524),
      (525),
      (526),
      (527)

WITH cte
AS
(
      SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber,
            (ROW_NUMBER() OVER(ORDER BY ID) - 1) / 5 AS GroupNumber,
            ID
      FROM #Test
)
SELECT DISTINCT GroupNumber,
      STUFF(
      (
            SELECT ',' + CAST(ID AS VARCHAR(20))
            FROM cte
            WHERE GroupNumber = cte1.GroupNumber
            FOR XML PATH('')
      ), 1, 1, '') AS IDList
FROM cte AS cte1
0
 
JRockFLAuthor Commented:
perfect!
i would have never figured that out.
thank you
0
 
Scott PletcherSenior DBACommented:
Very similar; not aware of the other, of course, as I was writing this :-) :


IF OBJECT_ID('tempdb.dbo.#IDs') IS NOT NULL
    DROP TABLE #IDs
CREATE TABLE #IDs (
    ID int,
    row_group int,
    PRIMARY KEY ( row_group, ID )
    )
INSERT INTO #IDs
SELECT ID, (ROW_NUMBER() OVER (ORDER BY ID) - 1) / 5 AS row_group
FROM dbo.tablename

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000000s].digit * 1000000 + [100000s].digit * 100000 + [10000s].digit * 10000 +
        [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]    
    CROSS JOIN cteDigits [10000s]
    CROSS JOIN cteDigits [100000s]
    CROSS JOIN cteDigits [1000000s]
)
SELECT STUFF((
    SELECT ',' + CAST(ID AS varchar(10))
    FROM #IDs i
    WHERE
        i.row_group = c.tally
    ORDER BY ID
    FOR XML PATH('')
    ), 1, 1, '')
FROM cteTally c
WHERE
    c.tally <= (SELECT COUNT(*) / 5 FROM #IDs)
ORDER BY
    c.tally
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.