原数据 200803 1 S5 C 4 5
200803 1 S5 S 1 2
200803 1 S5 G 2 3
200803 1 S5 K 5 6
200803 1 S5 J 6 7 目标数据 200803 1 S5 4 5 1 2 2 3 5 6 6 7
求一sql ,高分回报,谢谢
200803 1 S5 S 1 2
200803 1 S5 G 2 3
200803 1 S5 K 5 6
200803 1 S5 J 6 7 目标数据 200803 1 S5 4 5 1 2 2 3 5 6 6 7
求一sql ,高分回报,谢谢
SQL> SELECT * FROM TABLE_NAME;FIE1 FIE2 FIE3 FIE4 FIE5 FIE6
------ ---------- -------- ------ ---------- ----------
200803 1 S0015695 CHOKUS 4 5
200803 1 S0015695 SWROUK 1 2
200803 1 S0015695 GAICHU 2 3
200803 1 S0015695 KONYUS 5 6
200803 1 S0015695 JISEIS 6 7SQL> SELECT DISTINCT
2 FIE1,
3 FIE2,
4 FIE3,
5 REPLACE(WMSYS.WM_CONCAT(FIE5||' '||FIE6) OVER(PARTITION BY FIE1,FIE2,FIE3),',',' ') "FIE"
6 FROM TABLE_NAME TT;FIE1 FIE2 FIE3 FIE
------ ---------- -------- --------------------------------------------------------------------------------
200803 1 S0015695 4 5 1 2 2 3 5 6 6 7
wmsys.wm_concat
INSERT INTO TX1 VALUES (200803, 1, 'S5', 'S', 1, 2);
INSERT INTO TX1 VALUES (200803, 1, 'S5', 'G', 2, 3);
INSERT INTO TX1 VALUES (200803, 1, 'S5', 'K', 5, 6);
INSERT INTO TX1 VALUES (200803, 1, 'S5', 'J', 6, 7);
SELECT A,
B,
C,
MAX(DECODE(ROWNUM, 1, E)) D,
MAX(DECODE(ROWNUM, 1, F)) E,
MAX(DECODE(ROWNUM, 2, E)) F,
MAX(DECODE(ROWNUM, 2, F)) G,
MAX(DECODE(ROWNUM, 3, E)) H,
MAX(DECODE(ROWNUM, 3, F)) I,
MAX(DECODE(ROWNUM, 4, E)) J,
MAX(DECODE(ROWNUM, 4, F)) K,
MAX(DECODE(ROWNUM, 5, E)) L,
MAX(DECODE(ROWNUM, 5, F)) M
FROM TX1
GROUP BY A, B, C;
输出:
A B C D E F G H I J K L M
200803 1 S5 4 5 1 2 2 3 5 6 6 7