id gm
1 111
2 111
3 111
4 222
5 222
6 333
我要的结果是
gm count(gm) id id2 id3 .....idxn (未知个数)
111 3 1 2 3
222 2 4 5 NULL.....NULL
333 1 6
XXX N N1 N2 N3 ......NN
1 111
2 111
3 111
4 222
5 222
6 333
我要的结果是
gm count(gm) id id2 id3 .....idxn (未知个数)
111 3 1 2 3
222 2 4 5 NULL.....NULL
333 1 6
XXX N N1 N2 N3 ......NN
(
SELECT 1 AS ID,'111' AS gm FROM dual
UNION
SELECT 2 AS ID,'111' AS gm FROM dual
UNION
SELECT 3 AS ID,'111' AS gm FROM dual
UNION
SELECT 4 AS ID,'222' AS gm FROM dual
UNION
SELECT 5 AS ID,'222' AS gm FROM dual
UNION
SELECT 6 AS ID,'333' AS gm FROM dual
)
SELECT
gm,
count(*),
MAX(CASE cc WHEN 1 THEN cc ELSE NULL END) AS ID,
MAX(CASE cc WHEN 2 THEN cc ELSE NULL END) AS ID2,
MAX(CASE cc WHEN 3 THEN cc ELSE NULL END) AS ID3
FROM
(
SELECT
gm,
ID,
ROW_NUMBER() OVER(PARTITION BY t.gm ORDER BY gm) cc
FROM t
)
GROUP BY gm ORDER BY gm;
失误了,MAX(CASE cc WHEN 3 THEN cc ELSE NULL END)
改成 MAX(CASE cc WHEN 3 THEN id ELSE NULL END)
就ok了
create or replace procedure normalize AUTHID CURRENT_USER is
max_id_num number;
sqlstr varchar2(2500);
begin
select max(count(1))into max_id_num from test group by GM;
for r in 1..max_id_num loop
sqlstr:= sqlstr ||','|| 'MAX(CASE rk WHEN ' ||r|| 'THEN id ELSE NULL END) AS ID'||r;
end loop;
sqlstr := 'create or replace view v_test as SELECT gm,count(1) cnt'|| sqlstr || ' FROM (
SELECT
gm,
ID,
ROW_NUMBER() OVER(PARTITION BY test.gm ORDER BY gm) rk
FROM test
)
GROUP BY gm ORDER BY gm' ;
execute immediate sqlstr;
end normalize;--执行过程
begin
normalize;
end;
--查看结果
select * from v_test;
SQL> select * from v_test;
GM CNT ID1 ID2 ID3 ID4
--- ---------- ---------- ---------- ---------- ----------
111 4 1 2 3 7
222 2 5 4
333 1 6
SQL>
没有异常处理,写的比简单,楼主按照实际情况修改下