How can SQL create duplicate records?

sql

Solution

You can use a "numbers" table (it's handy for various operations):

CREATE TABLE num
( i UNSIGNED INT NOT NULL
, PRIMARY KEY (i)
) ;

INSERT INTO num (i)
VALUES
  (1), (2), ..., (1000000) ;

Then:

SELECT 
    t.id, t.name
FROM 
        tableX AS t
    JOIN
        num
            ON num.i <= t."count"

Warning: There is of course a limitation on this approach. The query will not produce all the wanted rows, as soon as you have a value in the `count` column that exceeds the maximum value stored in the Numbers table. If the values in the `count` column are unbounded, then only an iterative or recursive solution (like the other two answers) can work.

Problem

I was having a look at this question: Getting random value from a SQLite table and it got me wondering if one can duplicate records using SQL. More specifically, is there a construct that allows to transform these records: ``` id| name| count --------------- 1| Anne| 3 2| Joe| 2 ``` into: ``` id| name -------- 1| Anne 1| Anne 1| Anne 2| Joe 2| Joe ``` where the count of `Anne` and `Joe` records in the latter matches the corresponding `count` value in the former set of records. Can it be done with SQL? If so, how? EDIT: Lucero asks what SQL dialect; any really, but ideally the most agnostic approach would be the best. ANOTHER EDIT: Can it be done within a single SQL statement in say sqlite?

Original source