Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Concatenate 5 rows at a time

Posted on 2014-02-20
Medium Priority
225 Views
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
0
Question by:JRockFL
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 34

Accepted Solution

Brian Crowe earned 2000 total points
ID: 39875041
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

LVL 8

Author Comment

ID: 39875055
perfect!
i would have never figured that out.
thank you
0

LVL 70

Expert Comment

ID: 39875060
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month8 days, 1 hour left to enroll