17,382
社区成员




select m.字段1 ,
max(case n.px when 1 then n.字段X else '' end) 字段X1,
max(case n.px when 2 then n.字段X else '' end) 字段X2
from t1 m,
(select t.* , (select count(1) from t2 where 字段1 = t.字段1 and 字段X < t.字段X) + 1 px from t2 t) n
where m.字段1 = n.字段1
group by m.字段1
SQL> select * from t2;
C1 C2
---------- ----------
A X1
A X2
A X3
B Y1
C Z1
C Z2
6 rows selected
SQL>
SQL> select c1, max(decode(rn, 1, c2)) x1, max(decode(rn, 2, c2)) x2
2 from (select c1, c2, rn
3 from (SELECT C1,
4 C2,
5 ROW_NUMBER() OVER(partition by c1 order by c2) rn
6 from t2)
7 where rn <= 2)
8 group by c1
9 ;
C1 X1 X2
---------- ---------- ----------
A X1 X2
B Y1
C Z1 Z2
-- TRY IT ..
SQL> SELECT T1.FIELD1,
2 MAX(DECODE(RN,1,FIELDX,NULL)) FIELDX1,
3 MAX(DECODE(RN,2,FIELDX,NULL)) FIELDX2
4 FROM T1,
5 (SELECT T2.*,
6 ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELDX) RN
7 FROM T2
8 )TT
9 WHERE T1.FIELD1 = TT.FIELD1
10 GROUP BY T1.FIELD1;
FIELD1 FIELDX1 FIELDX2
------ ------- -------
A X1 X2
B Y1
C Z1 Z2
[Quote=引用楼主 lok9828 的帖子:]