一些SQL 和 存储过程

 --行列转换 行转列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal from dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal from dual CONNECT BY ROWNUM <= 4;
SELECT * from t_change_lc;
SELECT a.card_code,
       SUM(decode(a.q, 1, a.bal, 0)) q1,
       SUM(decode(a.q, 2, a.bal, 0)) q2,
       SUM(decode(a.q, 3, a.bal, 0)) q3,
       SUM(decode(a.q, 4, a.bal, 0)) q4
  from t_change_lc a
 GROUP BY a.card_code
 ORDER BY 1;
 
--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
       SUM(decode(a.q, 1, a.bal, 0)) q1,
       SUM(decode(a.q, 2, a.bal, 0)) q2,
       SUM(decode(a.q, 3, a.bal, 0)) q3,
       SUM(decode(a.q, 4, a.bal, 0)) q4
  from t_change_lc a
 GROUP BY a.card_code
 ORDER BY 1;
 
SELECT * from t_change_cl;
SELECT t.card_code,
       t.rn q,
       decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
  from (SELECT a.*, b.rn
          from t_change_cl a,
               (SELECT ROWNUM rn from dual CONNECT BY ROWNUM <= 4) b) t
 ORDER BY 1, 2;
 
--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q from t_change_lc;
SELECT * from t_change_lc_comma;
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
  from (SELECT a.card_co