行列转换-蒲公英云

行列转换

表一：

group c1 c2 c3 c4
-———————————————————————-
1 11 12 13 14
2 21 22 23 24
3 31 32 33 34

表二：

group c
-—————————————
1 11
1 12
1 13
1 14
2 21
2 22
2 23
2 24
3 31
3 32
3 33
3 34

由表一转到表二，是列转行
有表二转到表一，是行转列

行转列

with temp as
(
select 1 group_1,11 c1,12 c2,13 c3,14 c4 from dual union
select 2,21,22,23,24 from dual union
select 3,31,32,33,34 from dual
)
select group_1,
       regexp_substr(c,'[^,]+',1,n) c
from
(select group_1,c1||','||c2||','||c3||','||c4 c from temp),
(select rownum n from dual connect by rownum<=4)
where n<=length(regexp_replace(c,'[^,]',null)) + 1
order by group_1,c
列转行
方法一：
with temp as
(
select '张三' f1,'语文' f2,60 f3 from dual union select '张三' f1,'数学' f2,63 f3 from dual union select '张三' f1,'物理' f2,66 f3 from dual union select '张三' f1,'化学' f2,69 f3 from dual union
select '李四' f1,'语文' f2,61 f3 from dual union select '李四' f1,'数学' f2,64 f3 from dual union select '李四' f1,'物理' f2,67 f3 from dual union select '李四' f1,'化学' f2,70 f3 from dual union
select '王五' f1,'语文' f2,62 f3 from dual union select '王五' f1,'数学' f2,65 f3 from dual union select '王五' f1,'物理' f2,68 f3 from dual union select '王五' f1,'化学' f2,71 f3 from dual
)
select f1 XM,
       (select f3 from temp where f1 = t.f1 and f2='语文') 语文,
       (select f3 from temp where f1 = t.f1 and f2='数学') 数学,
       (select f3 from temp where f1 = t.f1 and f2='物理') 物理,
       (select f3 from temp where f1 = t.f1 and f2='化学') 化学
from temp t group by f1
方法二：
with temp as
(
select '张三' f1,'语文' f2,60 f3 from dual union select '张三' f1,'数学' f2,63 f3 from dual union select '张三' f1,'物理' f2,66 f3 from dual union select '张三' f1,'化学' f2,69 f3 from dual union
select '李四' f1,'语文' f2,61 f3 from dual union select '李四' f1,'数学' f2,64 f3 from dual union select '李四' f1,'物理' f2,67 f3 from dual union select '李四' f1,'化学' f2,70 f3 from dual union
select '王五' f1,'语文' f2,62 f3 from dual union select '王五' f1,'数学' f2,65 f3 from dual union select '王五' f1,'物理' f2,68 f3 from dual union select '王五' f1,'化学' f2,71 f3 from dual
)
select f1,
       sum(decode(f2,'语文', f3,null)) "语文",
       sum(decode(f2,'数学', f3,null)) "数学",
       sum(decode(f2,'物理', f3,null)) "物理",
       sum(decode(f2,'化学', f3,null)) "化学"
from temp group by f1