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: 
I'll assume

1. MyRef etc is a column in TableA
2. You have a [numbers](http://dataeducation.com/you-require-a-numbers-table/) table

Something like:

```
SELECT * INTO #TableA
FROM
 (
    SELECT  1 AS ID, 3 AS QUANTITY, 'MyRef' AS refColumn
    UNION ALL
    SELECT 2, 2, 'AnotherRef'
) T

;WITH Nbrs ( Number ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + Number FROM Nbrs WHERE Number < 99
)
SELECT
   A.ID, A.refColumn + CAST(N.Number AS varchar(10))
FROM
   #TableA A
   JOIN
   Nbrs N ON N.Number <= A.QUANTITY

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

```