데이터베이스 질문과 답변
<결과 1>
======================================
dpt gno name
======================================
10 A1 B1
10 A1 B2
10 A1 B3
10 A1 B4
10 A2 B5
10 A2 B6
10 A3 B7
10 A3 B8
10 A3 B9
10 A4 B10
20 A1 B11
20 A1 B12
20 A2 B13
20 A2 B14
20 A2 B15
20 A2 B16
20 A3 B17
20 A4 B18
20 A4 B19
20 A4 B20
<결과 2>
======================================
A1 A2 A3 A4 CNT
======================================
10 B1 B5 B7 B10 4
10 B2 B6 B8 3
10 B3 B9 2
10 B4 1
20 B11 B13 B17 B18 4
20 B12 B14 B19 3
20 B15 B20 2
20 B16 1
결과 1번을 결과 2번 모양으로 만들고 싶습니다.
WITH t AS ( SELECT 10 dpt, 'A1' gno, 'B1' name FROM dual UNION ALL SELECT 10, 'A1', 'B2' FROM dual UNION ALL SELECT 10, 'A1', 'B3' FROM dual UNION ALL SELECT 10, 'A1', 'B4' FROM dual UNION ALL SELECT 10, 'A2', 'B5' FROM dual UNION ALL SELECT 10, 'A2', 'B6' FROM dual UNION ALL SELECT 10, 'A3', 'B7' FROM dual UNION ALL SELECT 10, 'A3', 'B8' FROM dual UNION ALL SELECT 10, 'A3', 'B9' FROM dual UNION ALL SELECT 10, 'A4', 'B10' FROM dual UNION ALL SELECT 20, 'A1', 'B11' FROM dual UNION ALL SELECT 20, 'A1', 'B12' FROM dual UNION ALL SELECT 20, 'A2', 'B13' FROM dual UNION ALL SELECT 20, 'A2', 'B14' FROM dual UNION ALL SELECT 20, 'A2', 'B15' FROM dual UNION ALL SELECT 20, 'A2', 'B16' FROM dual UNION ALL SELECT 20, 'A3', 'B17' FROM dual UNION ALL SELECT 20, 'A4', 'B18' FROM dual UNION ALL SELECT 20, 'A4', 'B19' FROM dual UNION ALL SELECT 20, 'A4', 'B20' FROM dual ) SELECT dpt , MIN(DECODE(gno, 'A1', name)) a1 , MIN(DECODE(gno, 'A2', name)) a2 , MIN(DECODE(gno, 'A3', name)) a3 , MIN(DECODE(gno, 'A4', name)) a4 , COUNT(*) cnt FROM (SELECT dpt, gno, name , ROW_NUMBER() OVER(PARTITION BY dpt, gno ORDER BY name) rn FROM t ) GROUP BY dpt, rn ORDER BY dpt, rn ;
감사합니다~!
SELECT MIN(DECODE(gno, 'A1', name)) A1
, MIN(DECODE(gno, 'A2', name)) A2
, MIN(DECODE(gno, 'A3', name)) A3
, MIN(DECODE(gno, 'A4', name)) A4
FROM
(
SELECT a.*, ROW_NUMBER() OVER(PARTITION BY a.dpt, a.gno ORDER BY a.name) rn FROM T a
)
GROUP BY dpt, rn
ORDER BY dpt, rn
앗 올리고 보니 마농님이 이미.. 비슷해서 다행입니다 ㅎㅎ
감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.