Need to generate n rows based on a value in a column

user_196273 0
Viewed 33821

I have the following table

TABLE A

ID | QUANTITY
------------
1  | 3
2  | 2

What I need is

TABLE B

ID | Ref No.
------------
1  | MyRef1
1  | MyRef2
1  | MyRef3
2  | AnotherRef1
2  | AnotherRef2

i.e. I need to generate Table B with the same number of rows as the quantity in A with an ascending ref no. on each row.

I can do it with cursors or UDFs but is there a more graceful solution?

tsql sql-server
7 Answers

This would also do the trick. It uses recursion, creates a table with rows 1-100.

WITH NBR ( NUM ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + NUM FROM NBR 
    WHERE NUM < 100
)
SELECT * into NUMBERS from NBR
user_90813040

A very simple way to generate @n lines is the following

DECLARE @n INT= 50

SELECT
    RowId   = ROW_NUMBER()OVER(ORDER BY (SELECT 0))
FROM
    (VALUES(CAST(REPLICATE('<a/>',@n) AS XML)))xmlLines(xmlLines)
    CROSS APPLY xmlLines.nodes('*')Lines(Line)

This method has a limit. Since the REPLICATE function produces strings with a maximum size of 8000 characters, the lines produced can be at most 2000. To have a greater number you can use the following method:

DECLARE @n INT= 50;

WITH Block AS(
    SELECT
         RowId  = ROW_NUMBER()OVER(ORDER BY (SELECT 0))
        ,MaxBlockSize
    FROM
        (VALUES(128))MaxBlockSize(MaxBlockSize)
        CROSS APPLY (VALUES(CAST(REPLICATE('<a/>',CASE WHEN @n<MaxBlockSize THEN @n ELSE MaxBlockSize END) AS XML)))xmlLines(xmlLines)
        CROSS APPLY xmlLines.nodes('*')Lines(Line)
)
, Blocks AS(
    SELECT
         RowId          
        ,NextStart      = RowId*MaxBlockSize
    FROM
        Block
    WHERE
        RowId<=MaxBlockSize
    UNION ALL
    SELECT
         RowId          = Blocks.NextStart+Block.RowId          
        ,NextStart      = (Blocks.NextStart+Block.RowId)*MaxBlockSize
    FROM
        Blocks
        CROSS APPLY Block
    WHERE
        Blocks.NextStart+Block.RowId<=@n
)
SELECT RowId FROM Blocks ORDER BY RowId

the choice of the value of MaxBlockSize to 128 (maximum 2000 theoretically) has been reduced to 128 for reasons of query efficiency

user_152213600
Related