views:

47

answers:

1

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.

+3  A: 
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
Quassnoi
Nicely done there
ck
Msg 102, Level 15, State 1, Line 12Incorrect syntax near 'XML'.Msg 102, Level 15, State 1, Line 17Incorrect syntax near 'cbd'.
Coolcoder
I think you deserve a special badge if this works, so quick at responding too!
Coolcoder
Sure, just confused `ROOT` and `PATH` :) Try the updated query
Quassnoi
Amazing. Thank you very much.
Coolcoder