表结构
T
ID COL1
1 AAA
2 AAA
3 AAA
4 BBB
5 CCC
6 CCC
7 DDD
8 DDD
9 DDD
10 DDD
.....
.....查询结果为列1 列2 列3 列4
1 2 3 --(AAA)
4 --(BBB)
5 6 --(CCC)
7 8 9 10 --(DDD)
....
...数据组织规则为: COL1列中的项(AAA,BBB,CCC,....)没有限制项的数量,但每项的记录不超过10个,即查询之后的列的个数不定,但不会很多,有限.
这不是一个简单的行列转换,有谁知道怎么做?
T
ID COL1
1 AAA
2 AAA
3 AAA
4 BBB
5 CCC
6 CCC
7 DDD
8 DDD
9 DDD
10 DDD
.....
.....查询结果为列1 列2 列3 列4
1 2 3 --(AAA)
4 --(BBB)
5 6 --(CCC)
7 8 9 10 --(DDD)
....
...数据组织规则为: COL1列中的项(AAA,BBB,CCC,....)没有限制项的数量,但每项的记录不超过10个,即查询之后的列的个数不定,但不会很多,有限.
这不是一个简单的行列转换,有谁知道怎么做?
sum(decode(rn,2,id,null) 列2,
sum(decode(rn,3,id,null) 列3,
sum(decode(rn,4,id,null) 列4,
sum(decode(rn,5,id,null) 列5,
sum(decode(rn,6,id,null) 列6,
sum(decode(rn,7,id,null) 列7,
sum(decode(rn,8,id,null) 列8,
sum(decode(rn,9,id,null) 列9,
sum(decode(rn,10,id,null) 列10,
'--('||a.col1||')'from
(
select t.*,row_number() over (partition col1 order by id) rn
from t
) a
group by a.col1
max(case px when 1 then id end) 列1,
max(case px when 2 then id end) 列2,
max(case px when 3 then id end) 列3,
max(case px when 4 then id end) 列4,
max(case px when 5 then id end) 列5,
max(case px when 6 then id end) 列6,
max(case px when 7 then id end) 列7,
max(case px when 8 then id end) 列8,
max(case px when 9 then id end) 列9,
max(case px when 10 then id end) 列10
from
(
select * , px = (select count(1) from tb where col1 = t.col1 and id < t.id) + 1 from tb t
) m
group by col1
2 union
3 select 2 id,'AAA' col1 from dual
4 union
5 select 3 id,'AAA' col1 from dual
6 union
7 select 4 id,'BBB' col1 from dual
8 union
9 select 5 id,'CCC' col1 from dual
10 union
11 select 6 id,'CCC' col1 from dual
12 union
13 select 7 id,'DDD' col1 from dual
14 union
15 select 8 id,'DDD' col1 from dual
16 union
17 select 9 id,'DDD' col1 from dual
18 union
19 select 10 id,'DDD' col1 from dual
20 )
21 SELECT MAX(DECODE(RN,1,ID,NULL)) 列1,
22 MAX(DECODE(RN,2,ID,NULL)) 列2,
23 MAX(DECODE(RN,3,ID,NULL)) 列3,
24 MAX(DECODE(RN,4,ID,NULL)) 列4,
25 --MAX(DECODE(RN,5,ID,NULL)) 列5,
26 --MAX(DECODE(RN,6,ID,NULL)) 列6,
27 --MAX(DECODE(RN,7,ID,NULL)) 列7,
28 --MAX(DECODE(RN,8,ID,NULL)) 列8,
29 --MAX(DECODE(RN,9,ID,NULL)) 列9,
30 --MAX(DECODE(RN,10,ID,NULL)) 列10,
COL1
31 FROM (select ID,COL1,ROW_NUMBER()OVER(PARTITION BY COL1 ORDER BY ID,COL1) RN FROM A)
32 GROUP BY COL1
33 /
列1 列2 列3 列4 COL1
---------- ---------- ---------- ---------- ----
1 2 3 AAA
4 BBB
5 6 CCC
7 8 9 10 DDD