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_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 结果图标