데이터베이스 질문과 답변
SELECT 'Store01' AS CD, 10 AS WON, 1 AS YMD FROM DUAL UNION ALL
SELECT 'Store02' AS CD, 20 AS WON, 2 AS YMD FROM DUAL UNION ALL
SELECT 'Store03' AS CD, 30 AS WON, 3 AS YMD FROM DUAL UNION ALL
SELECT 'Store04' AS CD, 300 AS WON, 4 AS YMD FROM DUAL UNION ALL
SELECT 'Store05' AS CD, 54 AS WON, 5 AS YMD FROM DUAL UNION ALL
SELECT 'Store06' AS CD, 5 AS WON, 6 AS YMD FROM DUAL UNION ALL
SELECT 'Store07' AS CD, 8 AS WON, 7 AS YMD FROM DUAL
해당 쿼리의 결과를
Store01 | Store02| Store03| Store04| Store05| Store06| Store07
10 20 30 300 54 5 8
1 2 3 4 5 6 7
위의 형태의 결과값으로 얻는 방법을 알고 싶습니다.
해당 쿼리의 결과를
Store01 | Store02| Store03| Store04| Store05| Store06| Store07
10 20 30 300 54 5 8
1 2 3 4 5 6 7
위의 형태의 결과값으로 얻는 방법을 알고 싶습니다.
by 마농
[2014.02.25 15:20:39]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | WITH t AS(SELECT 'Store01' AS cd, 10 AS won, 1 AS ymd FROM dualUNION ALL SELECT 'Store02', 20, 2 FROM dualUNION ALL SELECT 'Store03', 30, 3 FROM dualUNION ALL SELECT 'Store04', 300, 4 FROM dualUNION ALL SELECT 'Store05', 54, 5 FROM dualUNION ALL SELECT 'Store06', 5, 6 FROM dualUNION ALL SELECT 'Store07', 8, 7 FROM dual)-- 2. Min(Decode(를 이용한 PivotSELECT DECODE(lv, 1, 'Won', 2, 'Ymd') gbn , MIN(DECODE(cd, 'Store01', v)) Store01 , MIN(DECODE(cd, 'Store02', v)) Store02 , MIN(DECODE(cd, 'Store03', v)) Store03 , MIN(DECODE(cd, 'Store04', v)) Store04 , MIN(DECODE(cd, 'Store05', v)) Store05 , MIN(DECODE(cd, 'Store06', v)) Store06 , MIN(DECODE(cd, 'Store07', v)) Store07 FROM (-- 1. Decode(lv 를 이용한 UnPivot SELECT lv , cd , DECODE(lv, 1, won, 2, ymd) v FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) ) GROUP BY lv ORDER BY lv; |
by 하트세이버
[2014.02.25 15:40:48]
감사합니다. 마농님 덕분에 해결할 수 있게 되었네요..
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(
) 버튼을 클릭하여 작성 하시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.