Select a row X times

Question

I have a very specific sql problem.

I have a table given with order positions (each position belongs to one order, but this isn't a problem):

|  Article ID  |  Amount  |
|--------------|----------|
|     5        |    3     |
|     12       |    4     |

For the customer, I need an export with every physical item that is ordered, e.g.

|  Article ID  |  Position  |
|--------------|------------|
|     5        |    1       |
|     5        |    2       |
|     5        |    3       |
|     12       |    1       |
|     12       |    2       |
|     12       |    3       |
|     12       |    4       |

How can I build my select statement to give me this results? I think there are two key tasks:

1) Select a row X times based on the amount

2) Set the position for each physical article

Your question may have an answer [here](http://stackoverflow.com/questions/18607669/select-rows-repeated-for-certain-count) and [here](http://stackoverflow.com/questions/10423767/sql-repeat-a-result-row-multiple-times-and-number-the-rows) — Alma Do, Nov 07 '13 at 07:39
Thank you! The first question doesn't number the rows, but maybe it's possible to do this in a second step.. The second looks good! Maybe there is a way (a table-valued function) to generate these number tables on the fly... — Andy, Nov 07 '13 at 07:58

peterm · Accepted Answer · 2013-11-07T08:13:27.490

7

You can do it like this

SELECT ArticleID, n.n Position
  FROM table1 t JOIN
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
) n
    ON n.n <= t.amount
 ORDER BY ArticleID, Position

Note: subquery n generates a sequence of numbers on the fly from 1 to 100. If you do a lot of such queries you may consider to create persisted tally(numbers) table and use it instead.

Here is SQLFiddle demo

or using a recursive CTE

WITH tally AS (
  SELECT 1 n
  UNION ALL
  SELECT n + 1 FROM tally WHERE n < 100
)
SELECT ArticleID, n.n Position
  FROM table1 t JOIN tally n
    ON n.n <= t.amount
 ORDER BY ArticleID, Position

Here is SQLFiddle demo

Output in both cases:

| ARTICLEID | POSITION |
|-----------|----------|
|         5 |        1 |
|         5 |        2 |
|         5 |        3 |
|        12 |        1 |
|        12 |        2 |
|        12 |        3 |
|        12 |        4 |

edited Nov 07 '13 at 08:13

answered Nov 07 '13 at 07:44

peterm

91,357
15
148
157

Thank you! Maybe there is a way to generate a number table on the fly via a table valued function. I'll check this and report the status! – Andy Nov 07 '13 at 08:00
@Andy MySQL doesn't support table valued functions – peterm Nov 07 '13 at 08:03
Oh, I tagged it mysql accidentally. I'm working on a mssql server! – Andy Nov 07 '13 at 08:09
@Andy It will work in SQL Server too. See updated answer for another solution using a recursive CTE. – peterm Nov 07 '13 at 08:18
@Andy Did it help? Do you need more help with your question? – peterm Nov 07 '13 at 17:43

score 1 · Answer 2 · answered Nov 08 '13 at 20:39

Query:

SQLFIDDLEExample

SELECT t1.[Article ID],
       t2.number 
FROM Table1 t1,
     master..spt_values t2
WHERE t1.Amount >= t2.number 
AND t2.type = 'P'
AND t2.number <= 255
AND t2.number <> 0

Result:

| ARTICLE ID | NUMBER |
|------------|--------|
|          5 |      1 |
|          5 |      2 |
|          5 |      3 |
|         12 |      1 |
|         12 |      2 |
|         12 |      3 |
|         12 |      4 |

Select a row X times

2 Answers2