Cust prod
1 A
1 B
1 C
2 A
2 B
Return
Cust Prod1 Prod2 Prod3
1 A B C
2 A B Null
怎么搞啊
------解决方案--------------------
慢慢搞
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是否相同...
------解决方案--------------------
--创建测试表 插入测试数据
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
------解决方案--------------------