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: 
This will create the number of rows you want, in SQL Server 2005+, though I'm not sure exactly how you want to determine what MyRef and AnotherRef should be...

```
WITH
  expanded
AS
(
  SELECT id, Quantity FROM myTable

UNION ALL

  SELECT id, Quantity - 1 FROM expanded WHERE Quantity > 1
)

SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY id, Quantity) AS unique_ref
FROM
  expanded
ORDER BY
  id,
  Quantity

```
Single select to generate rows by column TableA.quantity. Used only ISO/ANSI SQL Standard syntax 2003 (DB must support window function). 

TableA definition:

```
|----|----------|---------------|
| id | quantity | another_value |
|----|----------|---------------|
|  1 |         3|       value_a |
|  2 |         2|       value_b |
|  3 |         6|       value_c |
|----|----------|---------------|

CREATE TABLE TableA AS
  (SELECT 1 as ID, 3 AS quantity, 'value_a' AS another_value 
  UNION SELECT 2, 2, 'value_b' 
  UNION SELECT 3, 6, 'value_c');

```

The following query can be used for quantity value up to 1000. For quantity up to 10000 extend query by statement `CROSS JOIN ten AS rank10000`...

```
SELECT 
  ROW_NUMBER() OVER(order by id) as unique_id, 
  id as original_id, 
  another_value || ROW_NUMBER() OVER (PARTITION BY id) as another_value
FROM TableA
INNER JOIN
  (SELECT row_number() OVER () AS rnum FROM
    (WITH ten AS (SELECT 1 AS id UNION SELECT 2 
         UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 
         UNION SELECT 7 UNION SELECT 8 
         UNION SELECT 9 UNION SELECT 10)
     SELECT * 
     FROM ten AS rank10 
     CROSS JOIN ten AS rank100 
     CROSS JOIN ten AS rank1000
    ) helper
  ) help ON help.rnum <= TableA.quantity

```

SQL Result:

```
|-----------|-------------|---------------|
| unique_id | original_id | another_value |
|-----------|-------------|---------------|
|         1 |           1 |      value_a1 |
|         2 |           1 |      value_a2 |
|         3 |           1 |      value_a3 |
|         4 |           2 |      value_b1 |
|         5 |           2 |      value_b2 |
|         6 |           3 |      value_c1 |
|         7 |           3 |      value_c2 |
|         8 |           3 |      value_c3 |
|         9 |           3 |      value_c4 |
|        10 |           3 |      value_c5 |
|        11 |           3 |      value_c6 |
|-----------|-------------|---------------|

```

It should be work on PostgreSQL, Oracle or MSSQL (tested on PostgreSQL 9.0)

**Edited:**
Optimized query using statement **WITH RECURSIVE** (idea from MatBailie):

```
SELECT 
  ROW_NUMBER() OVER() as unique_id, 
  id as original_id, 
  another_value || ROW_NUMBER() OVER (PARTITION BY id) as another_value
FROM 
  (WITH RECURSIVE helper AS 
    (SELECT id, quantity, another_value FROM TableA 
     UNION ALL 
     SELECT id, quantity-1, another_value FROM helper WHERE quantity > 1
    ) SELECT * FROM helper ORDER BY id, quantity
  ) TableB

```