行列轉換
--行列轉換 行轉列
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_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
--行列轉換 列轉行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
SELECT * FROM t_change_cl_comma;
substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) > 0) t
ORDER BY 1, 2;[@more@]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9907339/viewspace-1045155/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql行列轉換詳解MySql
- 記錄一個行列轉換
- Oracle行列轉換及pivot子句的用法Oracle
- Spark實現行列轉換pivot和unpivotSpark
- Restcloud ETl實踐之資料行列轉換RESTCloud
- 使用vue實現行列轉換的一種方法。Vue
- 例項詳解構建數倉中的行列轉換
- 在報表中錄入資料時如何實現行列轉換
- 報表如何實現行列互換效果?
- 實現二維陣列的行列互換陣列
- excel列轉行怎麼做 excel如何轉置行列Excel
- SQL 如何實現動態的行列轉置SQL
- web 展現資料時如何實現行列互換Web
- 行列式
- pdf轉換成word,免費轉換
- js顯式轉換和隱式轉換JS
- AD模數轉換&DA數模轉換
- heic格式轉換jpg工具——轉易俠heic轉換器
- Linux 轉換換行符Linux
- 轉換流
- GUID轉換GUI
- 圖片格式怎麼轉換,如何轉換jpg
- PDF轉換CAD圖紙,如何快速轉換呢?
- unix時間轉換為datetimedatetime轉換為unixtime
- 用於日期轉換的訊息轉換器
- 雅可比行列式
- IDEA中進行列操作Idea
- 圖片格式轉換,JPG圖片轉換成PDF
- 顏色轉換
- Java —— 轉換流Java
- clang 轉換版本
- matlab版本轉換Matlab
- js轉換/Date(........)/JS
- Oracle轉換PostgresOracle
- 溫度轉換
- XSLT-轉換
- 編碼轉換
- 時間轉換
- Linq 日期轉換