Question: 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?

Answer: 
Following gives you the correct number of rows but my observation is the same as @Dems: How do you determine `MyRef` and `AnotherRef`?

*Note that this requires at least SQL Server 2005*

### SQL Statement

```
;WITH TableA (ID, Quantity) AS (
    SELECT  1, 3
    UNION ALL SELECT 2, 2
)
, q AS (
    SELECT  ID
            , Number = 1
            , Quantity
    FROM    TableA
    UNION ALL 
    SELECT  ID
            , Number = Number + 1
            , Quantity
    FROM    q
    WHERE   Quantity > Number
)
SELECT  ID
        , CASE WHEN ID = 1 THEN 'MyRef' + CAST(Number AS VARCHAR(1))
               WHEN ID = 2 THEN 'AnotherRef' + CAST(Number AS VARCHAR(1))
          END AS [Ref No.]
FROM    q       
ORDER BY
        ID

```

### Output

```
ID  Ref No.
1   MyRef1
1   MyRef2
1   MyRef3
2   AnotherRef1
2   AnotherRef2

```
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