CODEWITHSUNDEEP
sqlsql-servert-sql
Coolcoder
Coolcoder

Reputation: 4036

Batched output in SQL Server

I have a tables like the following

CREATE TABLE Company
(
Id INT
)

CREATE TABLE CompanyNumbers
(
CompanyId INT,
NumberText VARCHAR (255)
)

What I want as an output is this in pseudo code

Give me all the numbers for company A as a single comma separated string, if the string contains more than 150 numbers output another row with next 150 until complete.

What is the best way to achieve this? basically output batches of 150 numbers like this:

CompanyId | Batch
1         | 3344,444,5555,6444, 444, 44, 44555, 5555... > 150 of them 
2         | 33343,33, 2233,3 (second row if more than 150)

I want this to be done within a stored procedure.

Upvotes: 1

Views: 97

Answers (1)

Quassnoi
Quassnoi

Reputation: 425401

WITH    cb AS
        (
        SELECT  CompanyId, NumberText, ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY NumberText) AS rn
        FROM    CompanyNumbers
        )
SELECT  CompanyID, batch,
        (
        SELECT  CASE WHEN rn % 150 = 1 THEN '' ELSE ', ' END + NumberText AS [text()]
        FROM    cb
        WHERE   cb.CompanyID = cbd.CompanyID
                AND rn BETWEEN cbd.batch * 150 + 1 AND cbd.batch * 150 + 150
        FOR XML PATH('')
        )
FROM    (
        SELECT  DISTINCT CompanyID, FLOOR((rn - 1) / 150) AS batch
        FROM    cb
        ) AS cbd

Upvotes: 3

Related Questions