How can SQL create duplicate records?

Question

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?

Why would you want to? What would you do with the resulting data set? — Rory Hunter, Apr 09 '12 at 08:47
@RoryHunter: I'm curious that's all ;) no really I was thinking of how I could then randomly select from the result set — , Apr 09 '12 at 08:52
'Why?' - some quick performance testing is one reason. take your result set, crank it up 10x..100x.. to see how your JDBC driver and client code handles the volume. — rmckeown, Aug 09 '19 at 17:01

ypercubeᵀᴹ · Accepted Answer · 2012-10-21T08:13:37.367

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.

Ha nice join on the all the `num` records with `i<=count` – Apr 09 '12 at 09:00 — , Apr 09 '12 at 09:00

score 8 · Answer 2 · answered Apr 09 '12 at 08:48

8

What SQL dialect and version? It can eaily be done with a recursive CTE.

WITH cte AS (
    SELECT id, name, count FROM tbl
    UNION ALL
    SELECT id, name, count-1 FROM cte WHERE count>1
)
SELECT id, name FROM cte

answered Apr 09 '12 at 08:48

Lucero

59,176
9
122
152

score 3 · Answer 3 · answered Apr 09 '12 at 08:47

This can be done using a recursive CTE:

;WITH CTE_Data as (
    select id=1, name='Anne',[COUNT]=3
    union select id=2, name='Joe',[COUNT]=2
),
CTE_List as (
    select
        id,
        name,
        ind=1
    from CTE_Data
    union all
    select
        l.id,
        l.name,
        ind=ind+1
    from CTE_List l
        join CTE_Data d on l.id = d.id
    where 
        l.ind < d.[count]
)
select id,name from CTE_List
order by id,ind

NOTE: CTE_List is the interesting one, CTE_Data is just the source data for testing.

How can SQL create duplicate records?

3 Answers3

Linked