CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Mark
Mark

Reputation: 157

Oracle duplicate row N times where N is a column

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.

Upvotes: 6

Views: 7629

Answers (2)

Hogan
Hogan

Reputation: 70513

You can do this with a recursive cte. It would look like this

with cte as (name, dupcount, temp)
(
   select name,
          dupcount,
          dupcount as temp
   from testtable
     union all
   select name, 
          dupcount,
          temp-1 as temp
   from cte 
   where temp > 1
)
select name, 
       dupcount
from cte
order by name

Upvotes: 6

MT0
MT0

Reputation: 167877

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 |

Upvotes: 8

Related Questions