网上很多例子 ----------------建表 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;
用静态SQL语句实现转换该怎么写,就这个例子来说。
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 算是完成要求了,别的不评价。
可以用UNPIVOT进行转换,把“A,B,C,D”转换成行值,然后在用CASE语句把“一,二,三,四”转换成列属性。 代码如下: 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
----------------建表
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;
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