一行转多行效果(一)

一行转多行效果显示:

SQL>    with test as (    select 'a',4,10 from dual    union     select 'b',2,8 from dual)    select * from test;

'A'          4         10
--- ---------- ----------
a            4         10
b            2          8

需求:

 

    现有数据表中的数据形式为：  
      名称      数量     单价
       a       4      10
      b       2      8
   现在能否通过一个SQL语句将其变成(根据数量确定显示的记录条数)：
      名称            数量              单价
       a             1              10
      a             1              10
      a             1              10
      a             1              10
      b             1               8
      b             1               8    
         

结果:

SQL> with test as
  2  ( select 'a' name ,4 num ,10 price from dual
  3  union
  4  select 'b',2,8 from dual)
  5  select name,1 num,price ,rn from test ,(select rownum rn from dual connect by level<=9)
  6  where rn between 1 and num order by name
  7  ;

NAME        NUM      PRICE         RN
---- ---------- ---------- ----------
a             1         10          1
a             1         10          2
a             1         10          3
a             1         10          4
b             1          8          2
b             1          8          1

6 rows selected