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
3
Medium Priority
?
225 Views
Last Modified: 2014-02-20
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
Comment
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
  • Learn & ask questions
3 Comments
 
LVL 34

Accepted Solution

by:
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

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

Expert Comment

by:Scott Pletcher
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

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.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question