慢慢搞select Cust, MAX(decode(prod,'A','A')) Prod1, MAX(decode(prod,'B','B')) Prod2, MAX(decode(prod,'C','C')) Prod3 from ax GROUP BY Cust; CUST PROD1 PROD2 PROD3 ---------- ----- ----- ----- 1 A B C 2 A B
要看数据是否固定了 如果不确定行数 只能用动态sql判断了 还要判断prod是否相同...
不能这样decode取值。abc只是举例。真实的不是这样的
to 2f 这么设计动态语句啊
先不用判断prod是否相同。行数是不确定的
--创建测试表 插入测试数据 create table tb1 (cust number,prod nvarchar2(5)); insert into tb1 values (1,'A'); insert into tb1 values (1,'B'); insert into tb1 values (1,'C'); insert into tb1 values (2,'A'); insert into tb1 values (2,'B'); commit; --创建存储过程 CREATE OR REPLACE PROCEDURE T_TEST IS V_SQL NVARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT PROD FROM TB1 ORDER BY PROD;
BEGIN V_SQL := 'SELECT CUST '; FOR V_TB IN CURSOR_1 LOOP V_SQL := V_SQL || ',' || 'MAX(DECODE(T.PROD,'''|| V_TB.PROD || ''',T.PROD,NULL)) AS PROD_'|| V_TB.PROD ||''; END LOOP; V_SQL := V_SQL || ' FROM TB1 T' ; V_SQL := V_SQL || ' GROUP BY CUST'; DBMS_OUTPUT.PUT_LINE(V_SQL); --EXECUTE IMMEDIATE V_SQL; END; --执行存储过程 返回sql 执行结果 CUST PROD_A PROD_B PROD_C ------------------------------------------- 1 1 A B C 2 2 A B --继续插入测试数据 insert into tb1 values (2,'C'); insert into tb1 values (2,'D'); commit; --执行存储过程 返回查询语句 CUST PROD_A PROD_B PROD_C PROD_D ------------------------------------------- 1 1 A B C 2 2 A B C D--删除测试表和测试存储过程 --DROP TABLE TB1 --DROP PROCEDURE T_TEST
7、动态行转不定列 ----------------新建测试表 CREATE TABLE tmp_user_2(USER_ID NUMBER,MODE_NAME VARCHAR2(100),TYPE_ID NUmBER);----------------第一部分测试数据 INSERT INTO tmp_user_2 VALUES(1001, 'M1',1); INSERT INTO tmp_user_2 VALUES(1001, 'M2',2); INSERT INTO tmp_user_2 VALUES(1002, 'M1',3); INSERT INTO tmp_user_2 VALUES(1002, 'M2',4); INSERT INTO tmp_user_2 VALUES(1002, 'M3',5); INSERT INTO tmp_user_2 VALUES(1003, 'M1',6); COMMIT;----------------行转列存储过程 CREATE OR REPLACE PROCEDURE P_tmp_user_2 IS V_SQL VARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT T.MODE_NAME FROM tmp_user_2 T ORDER BY MODE_NAME;BEGIN V_SQL := 'SELECT USER_ID'; FOR V_XCLCK IN CURSOR_1 LOOP V_SQL := V_SQL || ',' || 'SUM(DECODE(MODE_NAME,''' || V_XCLCK.MODE_NAME || ''',TYPE_ID,0)) AS ' || V_XCLCK.MODE_NAME; END LOOP; V_SQL := V_SQL || ' FROM tmp_user_2 GROUP BY USER_ID'; --DBMS_OUTPUT.PUT_LINE(V_SQL); V_SQL := 'CREATE OR REPLACE VIEW tmp_user_3 AS ' || V_SQL; --DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL; END;----------------执行存储过程 BEGIN P_tmp_user_2; END;----------------查看结果 SELECT * FROM tmp_user_3;----------------第二部分测试数据 INSERT INTO tmp_user_2 VALUES(1003, 'M2',7); INSERT INTO tmp_user_2 VALUES(1004, 'M5',8); COMMIT;----------------执行存储过程 BEGIN P_tmp_user_2; END; ----------------查看结果 SELECT * FROM tmp_user_3;
MAX(decode(prod,'A','A')) Prod1,
MAX(decode(prod,'B','B')) Prod2,
MAX(decode(prod,'C','C')) Prod3
from ax
GROUP BY Cust; CUST PROD1 PROD2 PROD3
---------- ----- ----- -----
1 A B C
2 A B
这么设计动态语句啊
--创建测试表 插入测试数据
create table tb1 (cust number,prod nvarchar2(5));
insert into tb1 values (1,'A');
insert into tb1 values (1,'B');
insert into tb1 values (1,'C');
insert into tb1 values (2,'A');
insert into tb1 values (2,'B');
commit; --创建存储过程
CREATE OR REPLACE PROCEDURE T_TEST
IS
V_SQL NVARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT PROD
FROM TB1
ORDER BY PROD;
BEGIN
V_SQL := 'SELECT CUST ';
FOR V_TB IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'MAX(DECODE(T.PROD,'''|| V_TB.PROD || ''',T.PROD,NULL)) AS PROD_'|| V_TB.PROD ||'';
END LOOP;
V_SQL := V_SQL || ' FROM TB1 T' ;
V_SQL := V_SQL || ' GROUP BY CUST';
DBMS_OUTPUT.PUT_LINE(V_SQL);
--EXECUTE IMMEDIATE V_SQL;
END;
--执行存储过程 返回sql 执行结果 CUST PROD_A PROD_B PROD_C
-------------------------------------------
1 1 A B C
2 2 A B --继续插入测试数据
insert into tb1 values (2,'C');
insert into tb1 values (2,'D');
commit; --执行存储过程 返回查询语句 CUST PROD_A PROD_B PROD_C PROD_D
-------------------------------------------
1 1 A B C
2 2 A B C D--删除测试表和测试存储过程
--DROP TABLE TB1
--DROP PROCEDURE T_TEST
----------------新建测试表
CREATE TABLE tmp_user_2(USER_ID NUMBER,MODE_NAME VARCHAR2(100),TYPE_ID NUmBER);----------------第一部分测试数据
INSERT INTO tmp_user_2 VALUES(1001, 'M1',1);
INSERT INTO tmp_user_2 VALUES(1001, 'M2',2);
INSERT INTO tmp_user_2 VALUES(1002, 'M1',3);
INSERT INTO tmp_user_2 VALUES(1002, 'M2',4);
INSERT INTO tmp_user_2 VALUES(1002, 'M3',5);
INSERT INTO tmp_user_2 VALUES(1003, 'M1',6);
COMMIT;----------------行转列存储过程
CREATE OR REPLACE PROCEDURE P_tmp_user_2 IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT T.MODE_NAME FROM tmp_user_2 T ORDER BY MODE_NAME;BEGIN
V_SQL := 'SELECT USER_ID';
FOR V_XCLCK IN CURSOR_1 LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(MODE_NAME,''' || V_XCLCK.MODE_NAME ||
''',TYPE_ID,0)) AS ' || V_XCLCK.MODE_NAME;
END LOOP; V_SQL := V_SQL || ' FROM tmp_user_2 GROUP BY USER_ID';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE OR REPLACE VIEW tmp_user_3 AS ' || V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;----------------执行存储过程
BEGIN
P_tmp_user_2;
END;----------------查看结果
SELECT * FROM tmp_user_3;----------------第二部分测试数据
INSERT INTO tmp_user_2 VALUES(1003, 'M2',7);
INSERT INTO tmp_user_2 VALUES(1004, 'M5',8);
COMMIT;----------------执行存储过程
BEGIN
P_tmp_user_2;
END;
----------------查看结果
SELECT * FROM tmp_user_3;
Conn username/password@dbname
已连接
SQL> BEGIN
2 SP_TEST_ROW_TO_COL;
3 COMMIT;
4 END;
5 /PL/SQL 过程已成功完成。SQL>就这样,没有数据输出结果
我执行的 是返回sql语句 最后只是把拼接的动态sql语句打印出来 lz可以改成执行sql 返回数据集