동적으로 세로를 가로로 변경 1 2 1,925

by DISTINCT [SQL Query] sql query [2018.01.10 09:35:27]


가로로 된 테이블

WITH T AS ( SELECT 'MAN1' USER_NAME , 'M' GENDER , '4DAY' WORKDAY , '20180101' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN2' USER_NAME , 'M' GENDER , '1DAY' WORKDAY , '20180102' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN2' USER_NAME , 'M' GENDER , '2DAY' WORKDAY , '20180103' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '4DAY' WORKDAY , '20180104' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '4DAY' WORKDAY , '20180105' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '1DAY' WORKDAY , '20180106' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '2DAY' WORKDAY , '20180107' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '3DAY' WORKDAY , '20180108' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '3DAY' WORKDAY , '20180109' NALJJA FROM DUAL)
SELECT * FROM T;

 

위에 테이블을 이런 형식으로

 

WITH T AS ( SELECT 'MAN1' USER_NAME , 'M' GENDER , '20180106' ONEDAY ,  '20180107' TWODAY ,'20180109(2)' THREEDAY , '20180105(3)' FOURDAY FROM DUAL UNION ALL
            SELECT 'MAN2' USER_NAME , 'M' GENDER , '20180102' ONEDAY ,  '20180103' TWODAY ,'' THREEDAY , '' FOURDAY FROM DUAL )
SELECT * FROM T

 

변경 하고 싶습니다. 그런데 위에 예제는 4day 까지 있지만 앞으로 계속 5day ,6day , 7day ....이렇게 가변적으로 늘어 날꺼 같아요.;;; group by 써서 세로를 가로로 할경우는 컬럼이 정해진 경우에만 하는데...이렣게 가로 로 변경될 컬럼이 동적으로 늘어날 경우 어떻게 쿼리를 짜야 하죠?;;;

 

by 마농 [2018.01.10 10:09:06]

우선 정적 쿼리를 완성하시면 됩니다.
가변적으로 늘어나는 컬럼은 동적쿼리로 구현하셔야 합니다.
동적쿼리는 SQL영역이 아닌 프로그램 영역입니다.
정적쿼리로 한다면 한계를 정해놓고 구현하시면 됩니다.

WITH t AS
(
SELECT 'MAN1' user_name, 'M' gender, '4DAY' workday, '20180101' naljja FROM dual
UNION ALL SELECT 'MAN2', 'M', '1DAY', '20180102' FROM dual
UNION ALL SELECT 'MAN2', 'M', '2DAY', '20180103' FROM dual
UNION ALL SELECT 'MAN1', 'M', '4DAY', '20180104' FROM dual
UNION ALL SELECT 'MAN1', 'M', '4DAY', '20180105' FROM dual
UNION ALL SELECT 'MAN1', 'M', '1DAY', '20180106' FROM dual
UNION ALL SELECT 'MAN1', 'M', '2DAY', '20180107' FROM dual
UNION ALL SELECT 'MAN1', 'M', '3DAY', '20180108' FROM dual
UNION ALL SELECT 'MAN1', 'M', '3DAY', '20180109' FROM dual
)
SELECT user_name
     , gender
     , day1_dt || CASE WHEN day1_cnt > 1 THEN '('||day1_cnt||')' END day1
     , day2_dt || CASE WHEN day2_cnt > 1 THEN '('||day2_cnt||')' END day2
     , day3_dt || CASE WHEN day3_cnt > 1 THEN '('||day3_cnt||')' END day3
     , day4_dt || CASE WHEN day4_cnt > 1 THEN '('||day4_cnt||')' END day4
     , day5_dt || CASE WHEN day5_cnt > 1 THEN '('||day5_cnt||')' END day5
     , day6_dt || CASE WHEN day6_cnt > 1 THEN '('||day6_cnt||')' END day6
     , day7_dt || CASE WHEN day7_cnt > 1 THEN '('||day7_cnt||')' END day7
     , day8_dt || CASE WHEN day8_cnt > 1 THEN '('||day8_cnt||')' END day8
  FROM t
 PIVOT (MAX(naljja) dt, COUNT(*) cnt
        FOR workday IN ( '1DAY' day1, '2DAY' day2, '3DAY' day3, '4DAY' day4
                       , '5DAY' day5, '6DAY' day6, '7DAY' day7, '8DAY' day8
                       )
       )
;

 


by 삐르짱 [2018.01.10 10:52:16]
WITH T AS ( SELECT 'MAN1' USER_NAME , 'M' GENDER , '4DAY' WORKDAY , '20180101' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN2' USER_NAME , 'M' GENDER , '1DAY' WORKDAY , '20180102' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN2' USER_NAME , 'M' GENDER , '2DAY' WORKDAY , '20180103' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '4DAY' WORKDAY , '20180104' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '4DAY' WORKDAY , '20180105' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '1DAY' WORKDAY , '20180106' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '2DAY' WORKDAY , '20180107' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '3DAY' WORKDAY , '20180108' NALJJA FROM DUAL UNION ALL
            SELECT 'MAN1' USER_NAME , 'M' GENDER , '3DAY' WORKDAY , '20180109' NALJJA FROM DUAL)
SELECT USER_NAME
     , MAX(GENDER) AS GENDER 
     , MAX(DECODE(WORKDAY, '1DAY', NALJJA)) || MAX(DECODE(WORKDAY, '1DAY', CNT)) AS DAY1
     , MAX(DECODE(WORKDAY, '2DAY', NALJJA)) || MAX(DECODE(WORKDAY, '2DAY', CNT)) AS DAY2
     , MAX(DECODE(WORKDAY, '3DAY', NALJJA)) || MAX(DECODE(WORKDAY, '3DAY', CNT)) AS DAY3
     , MAX(DECODE(WORKDAY, '4DAY', NALJJA)) || MAX(DECODE(WORKDAY, '4DAY', CNT)) AS DAY4
     , MAX(DECODE(WORKDAY, '5DAY', NALJJA)) || MAX(DECODE(WORKDAY, '5DAY', CNT)) AS DAY5
     , MAX(DECODE(WORKDAY, '6DAY', NALJJA)) || MAX(DECODE(WORKDAY, '6DAY', CNT)) AS DAY6
     , MAX(DECODE(WORKDAY, '7DAY', NALJJA)) || MAX(DECODE(WORKDAY, '7DAY', CNT)) AS DAY7
     , MAX(DECODE(WORKDAY, '8DAY', NALJJA)) || MAX(DECODE(WORKDAY, '8DAY', CNT)) AS DAY8
     , MAX(DECODE(WORKDAY, '9DAY', NALJJA)) || MAX(DECODE(WORKDAY, '9DAY', CNT)) AS DAY9
     , MAX(DECODE(WORKDAY,'10DAY', NALJJA)) || MAX(DECODE(WORKDAY,'10DAY', CNT)) AS DAY10 /* 읽어야하는 최대치 일자에 대해서 요건을 협의해서 정해야 할 것 같습니다. 그렇치 않으면 프로그램(java, .. etc.) 을 통해서 쿼리를 동적으로 만들수 있지만 의미가 없어 보입니다. */
  FROM (SELECT USER_NAME
             , GENDER
             , WORKDAY
             , NALJJA
             , CASE WHEN COUNT(*) OVER(PARTITION BY USER_NAME, WORKDAY) > 1 THEN  '(' || COUNT(*) OVER(PARTITION BY USER_NAME, WORKDAY) || ')' END AS CNT
          FROM T
       )   
GROUP BY USER_NAME 
 ;

 

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