Oracle Row to Column 函式介绍

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_pivotGROUP 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_pivotGROUP 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 结果图示