求助，关于多行转多行多列的问题

水墨天夕 · 发表于 2018-12-27 13:58

本帖最后由水墨天夕于 2018-12-27 14:01 编辑

源表：
月份       入库       出库

01          1000       500
02          200       600
03          3000       3500
04          5000       3000
05          4000       2000

需要生成的表

类型    1月份    2月份 3月份    4月份    5月份
入库    1000    200    3000    5000       4000
出库    500       600    3500    3000       2000

我想问的是，如果使用unpiovt 和 piovt 函数能不能实现。
现在是使用的是union all来实现的。不知道还有没有其他更好的方法实现

qingfengfeixin · 发表于 2018-12-27 15:43

WITH T AS
(SELECT '01' AS MON, 1000 I, 500 O
FROM DUAL
UNION ALL
SELECT '02' AS MON, 200 I, 600 O
FROM DUAL
UNION ALL
SELECT '03' AS MON, 3000 I, 3500 O
FROM DUAL
UNION ALL
SELECT '04' AS MON, 5000 I, 3000 O
FROM DUAL
UNION ALL
SELECT '05' AS MON, 4000 I, 2000 O
FROM DUAL)
SELECT REGEXP_SUBSTR(JAN, '[^,]+', 1, LEVEL) AS JAN,
REGEXP_SUBSTR(FEB, '[^,]+', 1, LEVEL) AS FEB,
REGEXP_SUBSTR(MAR, '[^,]+', 1, LEVEL) AS MAR,
REGEXP_SUBSTR(APR, '[^,]+', 1, LEVEL) AS APR,
REGEXP_SUBSTR(MAY, '[^,]+', 1, LEVEL) AS MAY
FROM (SELECT *
FROM (SELECT I || ',' || O AS S, MON FROM T)
PIVOT(MAX(S)
FOR MON IN('01' AS JAN,
'02' AS FEB,
'03' AS MAR,
'04' AS APR,
'05' AS MAY)))
CONNECT BY LEVEL <= 2

复制代码

solomon_007 · 发表于 2018-12-27 16:35

SQL> with t as
  2 (select '01' as mon, 1000 in_val, 500 out_val
  3    from dual
  4 union all
  5 select '02', 200, 600
  6    from dual
  7 union all
  8 select '03', 3000, 3500
  9    from dual
10 union all
11 select '04', 5000, 3000
12    from dual
13 union all
14 select '05', 4000, 2000
15    from dual)
16  select 'In' flag,a.*
17 from (select mon,in_val from t)
18 pivot (
19          max(in_val) val
20          for mon in ('01' jan ,'02' feb ,'03' mar ,'04' apr ,'05' may )
21       ) a
22  union all
23  select 'Out',b.*
24 from (select mon,out_val from t)
25 pivot (
26          max(out_val) val
27          for mon in ('01' jan ,'02' feb ,'03' mar ,'04' apr ,'05' may )
28       ) b
29  /
FLAG JAN_VAL FEB_VAL MAR_VAL APR_VAL MAY_VAL
---- ---------- ---------- ---------- ---------- ----------
In       1000       200    3000    5000    4000
Out       500       600    3500    3000    2000

〇〇 · 发表于 2018-12-27 19:46

高级方法

newkid · 发表于 2018-12-27 23:21

with t as
(select '01' as mon, 1000 in_val, 500 out_val
from dual
  union all
  select '02', 200, 600
from dual
  union all
  select '03', 3000, 3500
from dual
union all
select '04', 5000, 3000
from dual
union all
select '05', 4000, 2000
from dual)
select *
  from (
select mon,val_type,val
from t
unpivot (val for val_type in (in_val as '入库',out_val as '出库'))
)
pivot (
      max(val) val
      for mon in ('01' jan ,'02' feb ,'03' mar ,'04' apr ,'05' may )
   )
;

newkid · 发表于 2018-12-27 23:25

11g之前的老写法：

with t as
(select '01' as mon, 1000 in_val, 500 out_val
from dual
  union all
  select '02', 200, 600
from dual
  union all
  select '03', 3000, 3500
from dual
union all
select '04', 5000, 3000
from dual
union all
select '05', 4000, 2000
from dual)
select val_type
   ,max(decode(mon,'01',decode(val_type,'入库',in_val,out_val))) as jan
   ,max(decode(mon,'02',decode(val_type,'入库',in_val,out_val))) as feb
   ,max(decode(mon,'03',decode(val_type,'入库',in_val,out_val))) as mar
   ,max(decode(mon,'04',decode(val_type,'入库',in_val,out_val))) as apr
   ,max(decode(mon,'05',decode(val_type,'入库',in_val,out_val))) as may
  from t,(select '入库' as val_type from dual union all select '出库' as val_type from dual)
group by val_type;

solomon_007 · 发表于 2018-12-28 10:00

newkid 发表于 2018-12-27 23:21
with t as (select '01' as mon, 1000 in_val, 500 out_val from dual union all select '02', 200, 6 ...

还是你这个写法好，UNPIVOT + PIVOT

水墨天夕 · 发表于 2018-12-28 10:35

qingfengfeixin 发表于 2018-12-27 15:43

感谢回复。

水墨天夕 · 发表于 2018-12-28 10:36

solomon_007 发表于 2018-12-27 16:35
SQL> with t as 2 (select '01' as mon, 1000 in_val, 500 out_val 3 from dual 4 union all ...

谢谢

水墨天夕 · 发表于 2018-12-28 10:37

newkid 发表于 2018-12-27 23:21
with t as (select '01' as mon, 1000 in_val, 500 out_val from dual union all select '02', 200, 6 ...

这个好，感谢

[PL/SQL] 求助，关于多行转多行多列的问题