怎样把列转换成行

显示全部楼层 · 2011-12-19 11:37:23

怎样把列转换成行, 例如:
DEPT
REASON
NUM
LEAN A
10
8
LEAN B
10
4
LEAN B
33
1
LEAN C
41
2
LEAN C
10
3
LEAN C
11
1
LEAN D
10
2
LEAN D
11
1
SELECT DEPT,REASON ,COUNT(REASON) FROM DUAL GROUP BY DEPT,REASON;
怎样能转换成
DEPT
REASON 10REASON 11 REASON 33 REASON 41
LEAN A 8
0
0
0
LEAN B 4
0
1
0
LEAN C 3
1
0
2
LEAN D 2
1
0
0

???SQL

千问 · 2011-12-19 11:37:23

It's a very old question!
SQL> select * from t99;
DEPTNO REASONNUM
------------ ---------- ----------
LEAN A
10
8
LEAN B
10
4
LEAN B
33
1
LEAN C
41
2
LEAN C
10
3
LEAN C
11
1
LEAN D
10
2
LEAN D
11
1
8 rows selected
SQL>
SQL> select DEPTNO,
2 max(decode(REASON, 10, NUM, 0)) "REASON 10",
3 max(decode(REASON, 11, NUM, 0)) "REASON 11",
4 max(decode(REASON, 33, NUM, 0)) "REASON 33",
5 max(decode(REASON, 41, NUM, 0)) "REASON 41"
6from t99
7group by DEPTNO;
DEPTNOREASON 10REASON 11REASON 33REASON 41
------------ ---------- ---------- ---------- ----------
LEAN A
8
0
0
0
LEAN B
4
0
1
0
LEAN C
3
1
0
2
LEAN D
2
1
0
0
SQL>

千问 · 2011-12-19 11:37:23

11g中可以用select的pivot子句实现；
11g之前的版本就如二楼所述的聚集函数实现之。

千问 · 2011-12-19 11:37:23

现在用11G的好像不多

千问 · 2011-12-19 11:37:23

如何實現?

千问 · 2011-12-19 11:37:23

谢谢各位的回复及关注,更要感谢2楼的处理方法.此问题已处理

千问 · 2011-12-19 11:37:23

处理方法:
select b.deptcD
, case when a.dutycd = '10' then count(a.em) else 0 endreson10
, case when a.dutycd = '30' then count(a.em) else 0 endreson30
, case when a.dutycd = '41' then count(a.em) else 0 endreson41
, case when a.dutycd in (11,31,32,33,40,51,60,61) then count(a.em) else 0 endreson_etc
from EFD a
, ECM b
where a.em = b.em
and a.sdutydat >='20080610'
and a.edutydat <='20080610'
group by b.deptcd, a.dutycd

千问 · 2011-12-19 11:37:23

坛里相关的资料好多，多看看吧LZ

千问 · 2011-12-19 11:37:23

我这里也没得11g玩，语法很简单，举个简单的例子：
WITH a AS(
SELECT 'c1' cname, 1 cvalue FROM dual UNION
SELECT 'c1' cname, 2 cvalue FROM dual UNION
SELECT 'c1' cname, 3 cvalue FROM dual UNION
SELECT 'c2' cname, 1 cvalue FROM dual UNION
SELECT 'c2' cname, 2 cvalue FROM dual UNION
SELECT 'c2' cname, 3 cvalue FROM dual UNION
SELECT 'c2' cname, 4 cvalue FROM dual)
SELECT cname, cv1, cv2, cv3, cv4
FROM a pivot(FOR cvalue IN 1 AS cv1, 2 AS cv2, 3 AS cv3, 4 AS cv4)
ORDER BY cname;
CNAME
CVALUE
-----------------------
c1
1
c1
2
c1
3
c2
4
CNAME
CV1
CV2
CV3
CV4
----------------------------------------------------------
c1
1
2
3

c2
1
2
3
4