oracle在线急等

[xiaoqinD]

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

[hfxl1108]

WITH t AS
(
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;

[hfxl1108]

引用 1 楼 hfxl1108 的回复:

WITH t AS ( 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……

失误了，MAX(CASE cc WHEN 3 THEN cc ELSE NULL END) 改成 MAX(CASE cc WHEN 3 THEN id ELSE NULL END) 就ok了

[yinan9]

为了达到动态效果，可以创一个存储过程来创建一个视图，当数据更新，执行过程来刷新视图数据

--创建过程
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;

[yinan9]

引用 3 楼 yinan9 的回复:

为了达到动态效果，可以创一个存储过程来创建一个视图，当数据更新，执行过程来刷新视图数据 SQL code?12345678910111213141516171819202122232425262728--创建过程create or replace procedure normalize AUTHID CURRENT_USER ismax_id_num number;sq……

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> 没有异常处理，写的比简单，楼主按照实际情况修改下

[善若止水]

高手，学习了