데이터베이스 질문과 답변
by 로빈짱 [2011.01.14 11:05:49]
이런 데이터가 있습니다.
-------------------------------------------
번호 | 날짜 | 주차
--------------------------------------------
1 | 2010-01-04 | 10
--------------------------------------------
2 | 2010-11-11 | 11
--------------------------------------------
3 | 2010-11-18 | 12
...
...
...
--------------------------------------------
이렇게 만들고 싶어서
-------------------------------------------------------------------------------
| 1 | 2 | 3 | .......
-------------------------------------------------------------------------------
| 2010-01-04 | 2010-11-11 | 2010-11-18 |
-------------------------------------------------------------------------------
| 10 | 11 | 12 |
------------------------------------------------------------------------------
WITH T AS
(
SELECT 1 No,'2010-01-04' DT,'10' WK FROM DUAL UNION ALL
SELECT 2, '2010-01-11', '11' FROM DUAL UNION ALL
SELECT 3, '2010-01-18', '12' FROM DUAL UNION ALL
SELECT 4, '2010-01-25', '13' FROM DUAL
)
SELECT *
FROM T, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 3) L
ORDER BY DT
번호 | 날짜 | 주차 | LV
---------------------------------------------------------
1 | 2010-01-04 | 10 | 1
1 | 2010-01-04 | 10 | 2
2 | 2010-01-11 | 11 | 1
2 | 2010-01-11 | 11 | 2
3 | 2010-01-18 | 12 | 2
3 | 2010-01-18 | 12 | 1
4 | 2010-01-25 | 13 | 2
4 | 2010-01-25 | 13 | 1
---------------------------------------------------------
이렇게 만들었습니다.
이다음에 저걸 컬럼으로 만들어야하는데
피벗관련글들을 보고 비슷하게 해보는데
잘안되네요. 어떻게 해야하나요. 도와주세요.
WITH T AS
(
SELECT 1 No,'2010-01-04' DT,'10' WK FROM DUAL UNION ALL
SELECT 2, '2010-01-11', '11' FROM DUAL UNION ALL
SELECT 3, '2010-01-18', '12' FROM DUAL UNION ALL
SELECT 4, '2010-01-25', '13' FROM DUAL
)
SELECT DECODE(NO, 1, DECODE(LV, 1, DT, 2, WK)), DECODE(NO, 2, DECODE(LV, 1, DT, 2, WK))
DECODE(NO, 3, DECODE(LV, 1, DT, 2, WK))
FROM T, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 3) L
ORDER BY DT
이런식으로 하면
-------------------------------------------------------------------------
2010-01-04 | 공백 | 공백 |
-------------------------------------------------------------------------
10 | 공백 | 공백 |
------------------------------------------------------------------------
공백 | 2010-01-11 | 공백 |
------------------------------------------------------------------------
공백 | 11 | 공백 |
------------------------------------------------------------------------
공백 | 공백 | 2010-01-18
------------------------------------------------------------------------
공백 | 공백 | 12
------------------------------------------------------------------------
이렇게 나와버려서
그건 다음 분이^^
select decode(b.no,1,regexp_substr(col1,'[^|]+',1,b.no)
,2,regexp_substr(col1,'[^|]+',1,b.no)
,3,regexp_substr(col1,'[^|]+',1,b.no)) as col1
, decode(b.no,1,regexp_substr(col2,'[^|]+',1,b.no)
,2,regexp_substr(col2,'[^|]+',1,b.no)
,3,regexp_substr(col2,'[^|]+',1,b.no)) as col2
, decode(b.no,1,regexp_substr(col3,'[^|]+',1,b.no)
,2,regexp_substr(col3,'[^|]+',1,b.no)
,3,regexp_substr(col3,'[^|]+',1,b.no)) as col3
from (
select max(decode(no,1,no||'|'||dt||'|'||wk)) as col1
, max(decode(no,2,no||'|'||dt||'|'||wk)) as col2
, max(decode(no,3,no||'|'||dt||'|'||wk)) as col3
, max(decode(no,4,no||'|'||dt||'|'||wk)) as col4
from t) a
,(select level as no
from dual
connect by level <= 3) b
급하게 쓴다보니^^
select regexp_substr(col1,'[^|]+',1,b.no) as col1
, regexp_substr(col2,'[^|]+',1,b.no) as col2
, regexp_substr(col3,'[^|]+',1,b.no) as col3
...
from (
select max(decode(no,1,no||'|'||dt||'|'||wk)) as col1
, max(decode(no,2,no||'|'||dt||'|'||wk)) as col2
, max(decode(no,3,no||'|'||dt||'|'||wk)) as col3
, max(decode(no,4,no||'|'||dt||'|'||wk)) as col4
from t) a
,(select level as no
from dual
connect by level <= 3) b
(
SELECT 1 No,'2010-01-04' DT,'10' WK FROM DUAL UNION ALL
SELECT 2, '2010-01-11', '11' FROM DUAL UNION ALL
SELECT 3, '2010-01-18', '12' FROM DUAL UNION ALL
SELECT 4, '2010-01-25', '13' FROM DUAL
)
SELECT REGEXP_SUBSTR(DECODE(LV,1,NO,2,DT,3,WK),'[^,]+',1,1) V1
,REGEXP_SUBSTR(DECODE(LV,1,NO,2,DT,3,WK),'[^,]+',1,2) V2
,REGEXP_SUBSTR(DECODE(LV,1,NO,2,DT,3,WK),'[^,]+',1,3) V3
,REGEXP_SUBSTR(DECODE(LV,1,NO,2,DT,3,WK),'[^,]+',1,4) V4
FROM (SELECT WM_CONCAT(NO) NO, WM_CONCAT(DT) DT, WM_CONCAT(WK) WK FROM T) A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 3) B
Group By lv 하셔야 두줄로 나오겠죠.
대신 Select절의 값은 그룹함수(Min, Max)로 묶어주시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.