데이터베이스 질문과 답변

행렬변환에 대한 질문입니다. 0 3 599

by 물통20병 [2018.05.28 17:50:44]

2015 11 A
2015 11 B
2015 11 C
2015 11 D
2015 12 E
2015 12 F
2015 12 G
2015 12 H
2016 01 I
2016 01 K

 

이런식의 데이터를

2015 11 A 2015 12 E 2016 01 I  
2015 11 B 2015 12 F 2016 01 K  
2015 11 C 2015 12 G        
2015 11 D 2015 12 H        

이런식으로 바꾸고 싶은데 어떤식으로 접근해야하나요?? 생각이 잘되지 않습니다.

by 마농 [2018.05.28 18:15:26] 
WITH t AS
(
SELECT '2015' yyyy, '11' mm, 'A' v FROM dual
UNION ALL SELECT '2015', '11', 'B' FROM dual
UNION ALL SELECT '2015', '11', 'C' FROM dual
UNION ALL SELECT '2015', '11', 'D' FROM dual
UNION ALL SELECT '2015', '12', 'E' FROM dual
UNION ALL SELECT '2015', '12', 'F' FROM dual
UNION ALL SELECT '2015', '12', 'G' FROM dual
UNION ALL SELECT '2015', '12', 'H' FROM dual
UNION ALL SELECT '2016', '01', 'I' FROM dual
UNION ALL SELECT '2016', '01', 'K' FROM dual
)
SELECT *
  FROM (SELECT yyyy, mm, v
             , DENSE_RANK() OVER(ORDER BY yyyy, mm) dr
             , ROW_NUMBER() OVER(PARTITION BY yyyy, mm ORDER BY v) rn
          FROM t
        )
 PIVOT (MIN(yyyy) y, MIN(mm) m, MIN(v) v FOR dr IN (1, 2, 3))
 ORDER BY rn
;

 

by 물통20병 [2018.05.28 18:34:23]

답변감사드립니다 10g 라 pivot이 없는데 그런경우 어떻게 생각해야하나요?

by 마농 [2018.05.28 18:39:25] 
WITH t AS
(
SELECT '2015' yyyy, '11' mm, 'A' v FROM dual
UNION ALL SELECT '2015', '11', 'B' FROM dual
UNION ALL SELECT '2015', '11', 'C' FROM dual
UNION ALL SELECT '2015', '11', 'D' FROM dual
UNION ALL SELECT '2015', '12', 'E' FROM dual
UNION ALL SELECT '2015', '12', 'F' FROM dual
UNION ALL SELECT '2015', '12', 'G' FROM dual
UNION ALL SELECT '2015', '12', 'H' FROM dual
UNION ALL SELECT '2016', '01', 'I' FROM dual
UNION ALL SELECT '2016', '01', 'K' FROM dual
)
SELECT rn
     , MIN(DECODE(dr, 1, yyyy)) y_1
     , MIN(DECODE(dr, 1, mm  )) m_1
     , MIN(DECODE(dr, 1, v   )) v_1
     , MIN(DECODE(dr, 2, yyyy)) y_2
     , MIN(DECODE(dr, 2, mm  )) m_2
     , MIN(DECODE(dr, 2, v   )) v_2
     , MIN(DECODE(dr, 3, yyyy)) y_3
     , MIN(DECODE(dr, 3, mm  )) m_3
     , MIN(DECODE(dr, 3, v   )) v_3
  FROM (SELECT yyyy, mm, v
             , DENSE_RANK() OVER(ORDER BY yyyy, mm) dr
             , ROW_NUMBER() OVER(PARTITION BY yyyy, mm ORDER BY v) rn
          FROM t
        )
 GROUP BY rn
 ORDER BY rn
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입