데이터베이스 질문과 답변
안녕하세요.
제가 쿼리를 좀 만들고 있는데,.
아래 자료까진 나왔는데 이것을 순서대로 각각 3줄 씩 나눠서 옆으로 컬럼으로 만들고 이를 묶을 수 있는 그룹번호를 생성해야하는데,
실력이 한참이나 모자라 도움을 구하고자 글을 올립니다.
---------------------------------
RANK CUST CODE
----------------------------------
1 0629379 X2
2 0038835 X2
3 0012469 X2
---- --------- ---
4 0019953 X2
5 0173159 X2
6 0173814 X2
---- --------- ---
7 0336774 B100
8 0252393 E30
9 0051319 D10
---- --------- ----
10 0183099 X2
11 0658635 X2
12 0008090 C100
---- --------- --
13 1198504 E10
14 0038834 C100
[얻고자 하는 결과]
---------------------------------------------------------------------------------------------
GRP RN CUST CODE RN CUST CODE RN CUST CODE
----------------------------------------------------------------------------------------------
1 1 0629379 X2 2 0038835 X2 3 0012469 X2
2 4 0019953 X2 5 0173159 X2 6 0173814 X2
3 7 0336774 B100 8 0252393 E30 9 0051319 D10
4 10 0183099 X2 11 0658635 X2 12 0008090 C100
5 13 1198504 E10 14 0038834 C100
WITH t AS
(
SELECT 1 rn, '0629379' cust, 'X2' code FROM dual
UNION ALL SELECT 2, '0038835', 'X2' FROM dual
UNION ALL SELECT 3, '0012469', 'X2' FROM dual
UNION ALL SELECT 4, '0019953', 'X2' FROM dual
UNION ALL SELECT 5, '0173159', 'X2' FROM dual
UNION ALL SELECT 6, '0173814', 'X2' FROM dual
UNION ALL SELECT 7, '0336774', 'B100' FROM dual
UNION ALL SELECT 8, '0252393', 'E30' FROM dual
UNION ALL SELECT 9, '0051319', 'D10' FROM dual
UNION ALL SELECT 10, '0183099', 'X2' FROM dual
UNION ALL SELECT 11, '0658635', 'X2' FROM dual
UNION ALL SELECT 12, '0008090', 'C100' FROM dual
UNION ALL SELECT 13, '1198504', 'E10' FROM dual
UNION ALL SELECT 14, '0038834', 'C100' FROM dual
)
SELECT grp
, MIN(DECODE(x, 1, rn )) rn1
, MIN(DECODE(x, 1, cust)) cust1
, MIN(DECODE(x, 1, code)) code1
, MIN(DECODE(x, 2, rn )) rn2
, MIN(DECODE(x, 2, cust)) cust2
, MIN(DECODE(x, 2, code)) code2
, MIN(DECODE(x, 3, rn )) rn3
, MIN(DECODE(x, 3, cust)) cust3
, MIN(DECODE(x, 3, code)) code3
FROM (SELECT rn, cust, code
, CEIL(rn / 3) grp
, MOD(rn - 1, 3) + 1 x
FROM t
)
GROUP BY grp
ORDER BY grp
;
WITH t AS
(
SELECT 1 rn, '0629379' cust, 'X2' code FROM dual
UNION ALL SELECT 2, '0038835', 'X2' FROM dual
UNION ALL SELECT 3, '0012469', 'X2' FROM dual
UNION ALL SELECT 4, '0019953', 'X2' FROM dual
UNION ALL SELECT 5, '0173159', 'X2' FROM dual
UNION ALL SELECT 6, '0173814', 'X2' FROM dual
UNION ALL SELECT 7, '0336774', 'B100' FROM dual
UNION ALL SELECT 8, '0252393', 'E30' FROM dual
UNION ALL SELECT 9, '0051319', 'D10' FROM dual
UNION ALL SELECT 10, '0183099', 'X2' FROM dual
UNION ALL SELECT 11, '0658635', 'X2' FROM dual
UNION ALL SELECT 12, '0008090', 'C100' FROM dual
UNION ALL SELECT 13, '1198504', 'E10' FROM dual
UNION ALL SELECT 14, '0038834', 'C100' FROM dual
)
SELECT *
FROM (SELECT rn, cust, code
, CEIL(rn / 3) grp
, MOD(rn - 1, 3) + 1 x
FROM t
)
PIVOT (MIN(rn) rn, MIN(cust) cust, MIN(code) code FOR x IN (1, 2, 3))
ORDER BY grp
;
마농님. 정말 감사합니다.
그것도 2가지 경우의 수로. 작성해서 주셨네요.. 많이 배우고 갑니다.
Pivot를 익혀서../ 이용해봐야겠습니다. .
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.