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 sol开发者_C百科ution?

I'll assume

1. MyRef etc is a column in TableA
2. You have a numbers 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

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

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

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 Numbers table solution will work in MS Access as long as the number of records in tblNumbers exceeds the maximum of Quantity values found in TableA:

SELECT TableA.ID, TableA.Quantity, tblNumbers.RecNum FROM TableA LEFT JOIN tblNumbers ON tblNumbers.RecNum <= TableA.Quantity ORDER BY TableA.ID, tblNumbers.RecNum;

NOTE: tblNumbers.RecNum is a Long Integer starting with row 1 = 1, row 2 = 2, etc.

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

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