|u| -- 行列转换

【例】（列转行）有一张表S，记录了某公司每个月的销售额，如下
Y Q AMT
2015 1 100
2015 2 110
2015 3 130
2015 4 100
2016 1 200
2016 2 150
2016 3 100
2016 4 300
转换成
Y Q1 Q2 Q3 Q4
2015 100 110 130 100
2016 200 150 100 300

朴实无华：子查询

SELECT (SELECT Y FROM S),
             (SELECT AMT FROM Y WHERE Q=1) Q1,
       (SELECT AMT FROM Y WHERE Q=2) Q2,
       (SELECT AMT FROM Y WHERE Q=3) Q3,
       (SELECT AMT FROM Y WHERE Q=4) Q4
FROM     DUAL

花里胡哨函数：DECODE

 SELECT S.Y,
       SUM(DECODE(S.Q, 1, AMT, NULL)) Q1,
       SUM(DECODE(S.Q, 2, AMT, NULL)) Q2,
       SUM(DECODE(S.Q, 3, AMT, NULL)) Q3,
       SUM(DECODE(S.Q, 4, AMT, NULL)) Q4
  FROM S
 GROUP BY S.Y

花里胡哨函数：CASE…WHEN…

 SELECT S.Y,
       SUM(CASE WHEN S.Q=1 THEN AMT END) Q1,
       SUM(CASE WHEN S.Q=2 THEN AMT END) Q2,
       SUM(CASE WHEN S.Q=3 THEN AMT END) Q3,
       SUM(CASE WHEN S.Q=4 THEN AMT END) Q4
  FROM S
 GROUP BY S.Y

位移函数：LEAD / LAG （很捞，建议不用）

 SELECT  S.Y, 
         S.AMT Q1, 
         S.LD1 Q2, 
         S.LD2 Q3, 
         S.LD3 Q4
FROM  (SELECT S.*,
              LEAD(S.AMT, 1) OVER(PARTITION BY S.Y ORDER BY S.Q) LD1,
              LEAD(S.AMT, 2) OVER(PARTITION BY S.Y ORDER BY S.Q) LD2,
              LEAD(S.AMT, 3) OVER(PARTITION BY S.Y ORDER BY S.Q) LD3
       FROM S) S
 WHERE S.Q = 1

——————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————

行转列：union all

WITH A AS
 (SELECT S.Y,
         SUM(DECODE(S.Q, 1, AMT, NULL)) Q1,
         SUM(DECODE(S.Q, 2, AMT, NULL)) Q2,
         SUM(DECODE(S.Q, 3, AMT, NULL)) Q3,
         SUM(DECODE(S.Q, 4, AMT, NULL)) Q4
    FROM S
   GROUP BY S.Y)    --将结果集命名为A，把A行转列
SELECT A.Y, 1, A.Q1
  FROM A
UNION ALL
SELECT A.Y, 2, A.Q2
  FROM A
UNION ALL
SELECT A.Y, 3, A.Q3
  FROM A
UNION ALL
SELECT A.Y, 4, A.Q4 FROM A