Oracle row to column在10G版本仅能使用 CASE or DECODE,11G版本才开始提供Pivot函式。
1.Create table and input data
CREATE TABLE TEST_PIVOT
(
CUSTOMER_ID VARCHAR2(10 BYTE),
CUSTOMER_NAME VARCHAR2(10 BYTE),
YYMM VARCHAR2(6 BYTE),
NT_PRICE NUMBER
)
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('A', 'AAA', '202001', 10000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('B', 'BBB', '202002', 5000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('C', 'CCC', '202003', 1000);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('D', 'DDD', '202004', 70);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('E', 'EEE', '202005', 76501);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('F', 'FFF', '202006', 1532);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('G', 'GGG', '202007', 5640);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('H', 'HHH', '202008', 8730);
insert into test_pivot (customer_id, customer_name, yymm, nt_price) values('I', 'III', '202009', 87530);
commit;
1.2 使用CASE语法
SELECT customer_id,
customer_name,
SUM (CASE WHEN yymm = '202001' THEN nt_price END) y202001,
SUM (CASE WHEN yymm = '202002' THEN nt_price END) y202002,
SUM (CASE WHEN yymm = '202003' THEN nt_price END) y202003,
SUM (CASE WHEN yymm = '202004' THEN nt_price END) y202004,
SUM (CASE WHEN yymm = '202005' THEN nt_price END) y202005,
SUM (CASE WHEN yymm = '202006' THEN nt_price END) y202006,
SUM (CASE WHEN yymm = '202007' THEN nt_price END) y202007,
SUM (CASE WHEN yymm = '202008' THEN nt_price END) y202008,
SUM (CASE WHEN yymm = '202009' THEN nt_price END) y202009
FROM test_pivot
GROUP BY customer_id, customer_name
1.3 使用DECODE语法
SELECT customer_id,
customer_name,
SUM (DECODE (yymm, '202001', nt_price)) y202001,
SUM (DECODE (yymm, '202002', nt_price)) y202002,
SUM (DECODE (yymm, '202003', nt_price)) y202003,
SUM (DECODE (yymm, '202004', nt_price)) y202004,
SUM (DECODE (yymm, '202005', nt_price)) y202005,
SUM (DECODE (yymm, '202006', nt_price)) y202006,
SUM (DECODE (yymm, '202007', nt_price)) y202007,
SUM (DECODE (yymm, '202008', nt_price)) y202008,
SUM (DECODE (yymm, '202009', nt_price)) y202009
FROM test_pivot
GROUP BY customer_id, customer_name
1.4 使用Pivot语法
SELECT *
FROM (SELECT yymm, customer_id, customer_name, nt_price FROM test_pivot)
PIVOT (SUM (nt_price)
FOR yymm
IN ('202001' y202001,
'202002' y202002,
'202003' y202003,
'202004' y202004,
'202005' y202005,
'202006' y202006,
'202007' y202007,
'202008' y202008,
'202009' y202009))
1.5 结果图示
Hi Dai Gei Ho~ 我是Winnie~ 今天是中秋连假的第一天,(而听主管说 通常连假什麽...
上篇回顾 设定档格式 YAML Docker太多文章介绍了, 小弟我K8S也没那麽熟稔 就介绍自己熟...
Set与Map不同再於Set没有key,是指有包含值的特殊集合,且每个值只能出现一次不能重复。 Se...
昨天介绍了BottomNavigationView的使用方法,今天我要分享使用BottomNavig...
本次预期会写一下的题目介绍 渗透测试基础 关於渗透测试/过程 工作和职业机会 练习 基本环境安装 V...