有: select kb1 from user
得到:
kb1
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j现在我想得到如下的结果,该怎么写呐?谢谢
new1 new2 new3 new4
1 a b c d
2 e f g h
3 i j
得到:
kb1
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j现在我想得到如下的结果,该怎么写呐?谢谢
new1 new2 new3 new4
1 a b c d
2 e f g h
3 i j
SELECT LEVEL ID, CHR(LEVEL+96) NAME FROM dual CONNECT BY LEVEL <= 10
)SELECT x.id1,
SUBSTR(name1,0,INSTR(name1,',',0)+1),
SUBSTR(name1,decode(INSTR(name1,',',1,1),0,NULL,INSTR(name1,',',1,1))+1,1),
SUBSTR(name1,decode(INSTR(name1,',',1,2),0,NULL,INSTR(name1,',',1,2))+1,1),
SUBSTR(name1,decode(INSTR(name1,',',1,3),0,NULL,INSTR(name1,',',1,3))+1,1)
FROM (
SELECT TRUNC(ID/4.1)+1 id1, wmsys.wm_concat(NAME) name1 FROM tmp
group BY TRUNC(ID/4.1)+1
) x
;
with t as(
select 1 id,'a' str from dual
union all
select 2,'b' from dual
union all
select 3,'c' from dual
union all
select 4,'d' from dual
union all
select 5,'e' from dual
union all
select 6,'f' from dual
union all
select 7,'g' from dual
union all
select 8,'h' from dual
union all
select 9,'i' from dual
union all
select 10,'j' from dual
)
select trunc((id - 1) / 4) id,
max(decode(mod(id, 4), 1, str)) new1,
max(decode(mod(id, 4), 2, str)) new2,
max(decode(mod(id, 4), 3, str)) new3,
max(decode(mod(id, 4), 0, str)) new4
from t
group by trunc((id - 1) / 4);
ID NEW1 NEW2 NEW3 NEW4
---------- ---- ---- ---- ----
0 a b c d
1 e f g h
2 i j