How to unfold the results of an Oracle query based

问题:

I have a table on a Oracle DB with two columns. I would like to see every row repeated as many times as the number stored in the second column. The table looks like this:

col1 col2  
a    2  
b    3  
c    1

I want to write a query that returns this:

col1 col2
a    2  
a    2   
b    3    
b    3    
b    3    
c    1

So the value from col2 dictates the number of times a row is repeated. Is there a simple way to achieve this?

Thanks!

回答1:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test ( col1, col2 ) AS
          SELECT 'a', 2 FROM DUAL
UNION ALL SELECT 'b', 3 FROM DUAL
UNION ALL SELECT 'c', 1 FROM DUAL

Query 1:

SELECT col1,
       col2
FROM   test t,
       TABLE(
         CAST( 
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= t.col2
           )
           AS SYS.ODCINUMBERLIST
         )
       )

Results:

| COL1 | COL2 |
|------|------|
|    a |    2 |
|    a |    2 |
|    b |    3 |
|    b |    3 |
|    b |    3 |
|    c |    1 |

回答2:

If a is your table then you can also do :

select a1.col1 , a1.col2 
      from a a1
      join  (select level col2  from dual 
      connect by level < ( select max(col2) + 1 from a))   a2
      on a2.col2 between 1 and a1.col2

回答3:

with src (col1, col2) as (
    SELECT 'a', 2 FROM DUAL UNION ALL
    SELECT 'b', 3 FROM DUAL UNION ALL
    SELECT 'c', 1 FROM DUAL
)
SELECT
    src.*
FROM
    src
    JOIN (
        SELECT LEVEL val FROM DUAL
        CONNECT BY LEVEL <= (SELECT MAX(col2) FROM src)
    ) gen ON gen.val <= src.COL2
ORDER BY
    src.col1

or in Oracle 12 is possible to use

with src (col1, col2) as (
    SELECT 'a', 2 FROM DUAL UNION ALL
    SELECT 'b', 3 FROM DUAL UNION ALL
    SELECT 'c', 1 FROM DUAL
)
SELECT
    src.*
FROM
    src
    CROSS APPLY (SELECT NULL FROM DUAL CONNECT BY LEVEL <= src.col2)
ORDER BY
    col1;

回答4:

Here's an alternative (I've added an extra column just to show that other columns can be present), assuming that col1 is unique:

with src (col1, col2, col3) as (
    SELECT 'a', 'b', 2 FROM DUAL UNION ALL
    SELECT 'b', 'c', 3 FROM DUAL UNION ALL
    SELECT 'c', 'd', 1 FROM DUAL
)
select col1, col2, col3
from   src
connect by level <= col3
           and prior col1 = col1
           and prior sys_guid() is not null;

COL1 COL2       COL3
---- ---- ----------
a    b             2
a    b             2
b    c             3
b    c             3
b    c             3
c    d             1