데이터베이스 질문과 답변
안녕하세요.
오라클로 쿼리를 작성하다 잘모르겠는 부분이 있어서 질문드립니다.
현재 아래와 같이 데이터가 있습니다.
| SEQ | REG_SEQ | AA | DT |
| 1 | 1 | YES | 2020-01-01 |
| 1 | 2 | YES | 2020-01-02 |
| 1 | 3 | YES | 2020-01-03 |
| 2 | 1 | NO | 2020-01-01 |
| 2 | 2 | ||
| 3 | 1 | YES | 2020-01-01 |
| 4 | 1 | ||
| 4 | 2 | YES | 2020-01-01 |
| 4 | 3 | YES | 2020-01-01 |
위 데이터를 아래와같이 SEQ별 한줄로 데이터가 출력되게 하고싶은데 어떤식으로 쿼리를 짜야되는걸까요?
| SEQ | REG_SEQ | AA | DT | REG_SEQ | AA | DT | REG_SEQ | AA | DT |
| 1 | 1 | YES | 2020-01-01 | 2 | YES | 2020-01-02 | 3 | YES | 2020-01-03 |
| 2 | 1 | NO | 2020-01-01 | 2 | |||||
| 3 | 1 | YES | 2020-01-01 | ||||||
| 4 | 1 | 2 | YES | 2020-01-01 | 3 | YES | 2020-01-01 |
WITH TEST AS (
SELECT '1' AS SEQ, '1' AS REG_SEQ, 'YES' AS AA, '2020-01-01' AS DT FROM DUAL
UNION ALL
SELECT '1' AS SEQ, '2' AS REG_SEQ, 'YES' AS AA, '2020-01-02' AS DT FROM DUAL
UNION ALL
SELECT '1' AS SEQ, '3' AS REG_SEQ, 'YES' AS AA, '2020-01-03' AS DT FROM DUAL
UNION ALL
SELECT '2' AS SEQ, '1' AS REG_SEQ, 'NO' AS AA, '2020-01-01' AS DT FROM DUAL
UNION ALL
SELECT '2' AS SEQ, '2' AS REG_SEQ, '' AS AA, '' AS DT FROM DUAL
UNION ALL
SELECT '3' AS SEQ, '1' AS REG_SEQ, 'YES' AS AA, '2020-01-01' AS DT FROM DUAL
UNION ALL
SELECT '4' AS SEQ, '1' AS REG_SEQ, '' AS AA, '' AS DT FROM DUAL
UNION ALL
SELECT '4' AS SEQ, '2' AS REG_SEQ, 'YES' AS AA, '2020-01-01' AS DT FROM DUAL
UNION ALL
SELECT '4' AS SEQ, '3' AS REG_SEQ, 'YES' AS AA, '2020-01-01' AS DT FROM DUAL
)
SELECT * FROM TEST;
WITH test AS
(
SELECT 1 seq, 1 reg_seq, 'YES' aa, '2020-01-01' dt FROM dual
UNION ALL SELECT 1, 2, 'YES', '2020-01-02' FROM dual
UNION ALL SELECT 1, 3, 'YES', '2020-01-03' FROM dual
UNION ALL SELECT 2, 1, 'NO' , '2020-01-01' FROM dual
UNION ALL SELECT 2, 2, '' , '' FROM dual
UNION ALL SELECT 3, 1, 'YES', '2020-01-01' FROM dual
UNION ALL SELECT 4, 1, '' , '' FROM dual
UNION ALL SELECT 4, 2, 'YES', '2020-01-01' FROM dual
UNION ALL SELECT 4, 3, 'YES', '2020-01-01' FROM dual
)
SELECT *
FROM test
PIVOT ( MIN(aa) aa
, MIN(dt) dt
FOR reg_seq IN (1, 2, 3)
)
ORDER BY seq
;
WITH test AS
(
SELECT 1 seq, 1 reg_seq, 'YES' aa, '2020-01-01' dt FROM dual
UNION ALL SELECT 1, 2, 'YES', '2020-01-02' FROM dual
UNION ALL SELECT 1, 3, 'YES', '2020-01-03' FROM dual
UNION ALL SELECT 2, 1, 'NO' , '2020-01-01' FROM dual
UNION ALL SELECT 2, 2, '' , '' FROM dual
UNION ALL SELECT 3, 1, 'YES', '2020-01-01' FROM dual
UNION ALL SELECT 4, 1, '' , '' FROM dual
UNION ALL SELECT 4, 2, 'YES', '2020-01-01' FROM dual
UNION ALL SELECT 4, 3, 'YES', '2020-01-01' FROM dual
)
SELECT seq
, MIN(DECODE(reg_seq, 1, aa)) aa_1
, MIN(DECODE(reg_seq, 1, dt)) dt_1
, MIN(DECODE(reg_seq, 2, aa)) aa_2
, MIN(DECODE(reg_seq, 2, dt)) dt_2
, MIN(DECODE(reg_seq, 3, aa)) aa_3
, MIN(DECODE(reg_seq, 3, dt)) dt_3
FROM test
GROUP BY seq
ORDER BY seq
;
마농님 감사합니다.
pivot을 활용해서 구현해보고 있었는데
group by 를 통해서도 가능한 쿼리였군요.
너무너무 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.