Oracle duplicate row N times where N is a column

oracle, oracle10g, sql

Solution

You can do it with a hierarchical query:

SQL Fiddle

Query 1:

WITH levels AS (
  SELECT LEVEL AS lvl
  FROM   DUAL
  CONNECT BY LEVEL <= ( SELECT MAX( DupCount ) FROM TestTable )
)
SELECT Name,
       DupCount
FROM   TestTable
       INNER JOIN
       levels
       ON ( lvl <= DupCount )
ORDER BY Name

Results:

|  NAME | DUPCOUNT |
|-------|----------|
|  Jane |        1 |
|  Jeff |        3 |
|  Jeff |        3 |
|  Jeff |        3 |
|  Mark |        2 |
|  Mark |        2 |
| Steve |        1 |

Problem

I'm new to Oracle and I'm trying to do something a little unusual. Given this table and data I need to select each row, and duplicate ones where DupCount is greater than 1. ``` create table TestTable ( Name VARCHAR(10), DupCount NUMBER ) INSERT INTO TestTable VALUES ('Jane', 1); INSERT INTO TestTable VALUES ('Mark', 2); INSERT INTO TestTable VALUES ('Steve', 1); INSERT INTO TestTable VALUES ('Jeff', 3); ``` Desired Results: ``` Name DupCount --------- ----------- Jane 1 Mark 2 Mark 2 Steve 1 Jeff 3 Jeff 3 Jeff 3 ``` If this isn't possible via a single select statement any help with a stored procedure would be greatly appreciated.

Original source