SQL行列互换
请教如何用SQL将数据从表1转为表2展现？
[解决办法]
网上很多例子
----------------建表
CREATE TABLE TEST(
WL VARCHAR2(10),
XYSL INTEGER,
XYCK VARCHAR2(10),
XCLCK VARCHAR2(10),
XCLCKSL INTEGER,
PC INTEGER
);
----------------第一部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223);
COMMIT;
--select * from test;
---------------------------行转列的存储过程
CREATE OR REPLACE PROCEDURE P_TEST IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;
BEGIN
V_SQL := 'SELECT WL,XYSL,XYCK';
FOR V_XCLCK IN CURSOR_1
LOOP
V_SQL := V_SQL
[解决办法]
','
[解决办法]
'SUM(DECODE(XCLCK,'''
[解决办法]
V_XCLCK.XCLCK
[解决办法]
''',XCLCKSL,0)) AS '
[解决办法]
V_XCLCK.XCLCK;
END LOOP;
V_SQL := V_SQL
[解决办法]
' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '
[解决办法]
V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
----------------------------结果
----------------执行存储过程，生成视图
BEGIN
P_TEST;
END;
----------------结果
SELECT * FROM RESULT T;
WL XYSL XYCK C1 C2 C3 C4 C5
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
A1 2 C1 20 50 10 0 0
A2 3 C4 40 0 0 110 70
----------------第二部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' , 20, 124);
INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' , 30, 121);
INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' , 20, 322);
COMMIT;
----------------报告存储过程，生成视图
BEGIN
P_TEST;
END;
----------------结果
SELECT * FROM RESULT T;
WL XYSL XYCK C1 C2 C3 C4 C5 C6 C7 C8
----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------
A1 2 C1 20 50 10 0 0 20 0 0
A2 2 C1 0 0 0 0 0 0 30 0
A2 3 C4 40 0 0 110 70 0 0 0
A3 2 C1 0 0 0 0 0 0 0 20
--------------- 删除实体
DROP VIEW RESULT;
DROP PROCEDURE P_TEST;
DROP TABLE TEST;
WITH TEST AS (
SELECT 'ONE' AS FLG,'1' AS A ,'2' AS B,'3' AS C,'4' AS D FROM DUAL
UNION ALL
SELECT 'TWO' AS FLG,'8' AS A ,'7' AS B,'6' AS C,'5' AS D FROM DUAL
UNION ALL
SELECT 'THREE' AS FLG,'9' AS A ,'10' AS B,'11' AS C,'12' AS D FROM DUAL
UNION ALL
SELECT 'FOUR' AS FLG,'16' AS A ,'15' AS B,'14' AS C,'13' AS D FROM DUAL
)
select decode(str, 1, 'A', 2, 'B', 3, 'C', 4, 'C') AS STR,
max(ONE) as ONE,
max(TWO) as TWO,
max(THREE) as THREE,
max(FOUR) as FOUR
from (select NTILE(4) OVER(order by null) as str, T.*
from (select DECODE(FLG,
'ONE',
decode(lvl, 1, A, 2, B, 3, C, 4, D),
'') as ONE,
DECODE(FLG,
'TWO',
decode(lvl, 1, A, 2, B, 3, C, 4, D),
'') as TWO,
DECODE(FLG,
'THREE',
decode(lvl, 1, A, 2, B, 3, C, 4, D),
'') as THREE,
DECODE(FLG,
'FOUR',
decode(lvl, 1, A, 2, B, 3, C, 4, D),
'') as FOUR
from TEST,
(select level lvl from dual connect by level <= 4)) T)
group by str
order by str
WITH TEMP1 AS(
SELECT FID,CHAR_ID,NUM
FROM
(
SELECT '一' AS FID,1 AS "A",2 AS "B",3 AS "C",4 AS "D" FROM DUAL UNION ALL
SELECT '二' ,8,7,6,5 FROM DUAL UNION ALL
SELECT '三',9,10,11,12 FROM DUAL UNION ALL
SELECT '四',16,15,14,13 FROM DUAL
) T
UNPIVOT
(NUM FOR CHAR_ID
IN("A","B","C","D")
)P
)
SELECT CHAR_ID,
MAX(CASE WHEN FID='一' THEN NUM END) AS "一",
MAX(CASE WHEN FID='二' THEN NUM END) AS "二",
MAX(CASE WHEN FID='三' THEN NUM END) AS "三",
MAX(CASE WHEN FID='四' THEN NUM END) AS "四"
FROM TEMP1
GROUP BY CHAR_ID
ORDER BY CHAR_ID
------------结果-------------------------
CHAR_ID 一 二 三 四
A 1 8 9 16
B 2 7 10 15
C 3 6 11 14
D 4 5 12 13
