我有一张这样的桌子。
ID Month Event Count
-----------------------------------------------------
1 Jan A 5
1 Jan B 4
1 Feb B 3
2 Feb A 12
2 March B 8
2 April C 5
现在我想要的是将唯一的事件类型转换为单独的列,并为每个列放置各自的计数。结果应该是这样的——
ID Month A B C
----------------------------------------------------------
1 Jan 5 4 0
1 Feb 0 3 0
2 Feb 12 0 0
2 March 0 8 0
2 April 0 0 5
我正在使用甲骨文。我能想到的是这样的——
select
ID,
Month,
nvl(a,0) a,
nvl(b,0) b,
nvl(c,0) c,
nvl(d,0) d,
nvl(e,0) e,
nvl(f,0) f
from
t
PIVOT
(
sum(Count) for (Event) in (
1 as a,
2 as b,
3 as c,
4 as d,
5 as e,
6 as f
)
)
很明显,这段代码是行不通的。我需要找到一些在旋转时考虑月份列的东西。有没有好的方法可以做到这一点?
带有子查询。查看代码中的注释。
SQL> WITH
2 -- sample data
3 test (id,
4 month,
5 event,
6 ccount)
7 AS
8 (SELECT 1, 'jan', 'a', 5 FROM DUAL UNION ALL
9 SELECT 1, 'jan', 'b', 4 FROM DUAL UNION ALL
10 SELECT 1, 'feb', 'b', 3 FROM DUAL UNION ALL
11 SELECT 2, 'feb', 'a', 12 FROM DUAL UNION ALL
12 SELECT 2, 'mar', 'b', 8 FROM DUAL UNION ALL
13 SELECT 2, 'apr', 'c', 5 FROM DUAL)
14 -- use PIVOT query as a subquery, and apply NVL to columns fetched from it
15 SELECT id,
16 month,
17 NVL (a, 0) a,
18 NVL (b, 0) b,
19 NVL (c, 0) c
20 FROM (SELECT *
21 FROM test
22 PIVOT (SUM (ccount)
23 FOR event
24 IN ('a' AS a, 'b' AS b, 'c' AS c)))
25 ORDER BY id, TO_CHAR(TO_DATE(month, 'mon'), 'mm');
ID MON A B C
---------- --- ---------- ---------- ----------
1 jan 5 4 0
1 feb 0 3 0
2 feb 12 0 0
2 mar 0 8 0
2 apr 0 0 5
SQL>
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句