ms sql 쿼리 결과값 합치고 싶은데 어떻게 해야 할까요 0 2 107

by 한청이 [SQL Query] [2021.04.06 16:14:28]


캡처.PNG (16,098Bytes)

select t1.empnum,
        mgyear = '2020',
        case max(date01)when 0 then '0' else CONVERT(nvarchar(3),max(date01)) end as date01,
            case max(date02)when 0 then '0' else CONVERT(nvarchar(3),max(date02)) end as date02,
            case max(date03)when 0 then '0' else CONVERT(nvarchar(3),max(date03)) end as date03,
            case max(date04)when 0 then '0' else CONVERT(nvarchar(3),max(date04)) end as date04,
            case max(date05)when 0 then '0' else CONVERT(nvarchar(3),max(date05)) end as date05,
            case max(date06)when 0 then '0' else CONVERT(nvarchar(3),max(date06)) end as date06,
            case max(date07)when 0 then '0' else CONVERT(nvarchar(3),max(date07)) end as date07,
            case max(date08)when 0 then '0' else CONVERT(nvarchar(3),max(date08)) end as date08,
            case max(date09)when 0 then '0' else CONVERT(nvarchar(3),max(date09)) end as date09,
            case max(date10)when 0 then '0' else CONVERT(nvarchar(3),max(date10)) end as date10,
            case max(date11)when 0 then '0' else CONVERT(nvarchar(3),max(date11)) end as date11,
            case max(date12)when 0 then '0' else CONVERT(nvarchar(3),max(date12)) end as date12,
        useday = (select sum (annual) from ANNUAL_COPY where smonth like '2020%'and empnum = t1.empnum)
         
         from ( select
        anc.empnum
        ,date01 = (select anc.annual where anc.smonth =  concat('2020','01'))
        ,date02 = (select anc.annual where anc.smonth =  concat('2020','02'))
        ,date03 = (select anc.annual where anc.smonth =  concat('2020','03'))
        ,date04 = (select anc.annual where anc.smonth =  concat('2020','04'))
        ,date05 = (select anc.annual where anc.smonth =  concat('2020','05'))
        ,date06 = (select anc.annual where anc.smonth =  concat('2020','06'))
        ,date07 = (select anc.annual where anc.smonth =  concat('2020','07'))
        ,date08 = (select anc.annual where anc.smonth =  concat('2020','08'))
        ,date09 = (select anc.annual where anc.smonth =  concat('2020','09'))
        ,date10 = (select anc.annual where anc.smonth =  concat('2020','10'))
        ,date11 = (select anc.annual where anc.smonth =  concat('2020','11'))
        ,date12 = (select anc.annual where anc.smonth =  concat('2020','12'))
        from ANNUAL_COPY anc  where  SUBSTRING(anc.smonth,1,4) = '2020') t1 , 
        ANNUAL_COPY anc where t1.empnum = anc.empnum
        group by t1.empnum, anc.smonth, anc.annual
        order by empnum
        

쿼리문은 이렇게 했고 ANNUAL_COPY의 테이블은 

empnum, smonth, annual  

 아이디,   년월 ,     횟수   

라고 생각하시면 되겠습니다. 

쿼리 결과 값이 월마다 나와서 이 결과를 년으로만 뜨게 하고 싶습니다. 

by 마농 [2021.04.06 19:09:40]
SELECT empnum
     , mgyear
     , ISNULL(SUM(CASE mm WHEN '01' THEN annual END), 0) m01
     , ISNULL(SUM(CASE mm WHEN '02' THEN annual END), 0) m02
     , ISNULL(SUM(CASE mm WHEN '03' THEN annual END), 0) m03
     , ISNULL(SUM(CASE mm WHEN '04' THEN annual END), 0) m04
     , ISNULL(SUM(CASE mm WHEN '05' THEN annual END), 0) m05
     , ISNULL(SUM(CASE mm WHEN '06' THEN annual END), 0) m06
     , ISNULL(SUM(CASE mm WHEN '07' THEN annual END), 0) m07
     , ISNULL(SUM(CASE mm WHEN '08' THEN annual END), 0) m08
     , ISNULL(SUM(CASE mm WHEN '09' THEN annual END), 0) m09
     , ISNULL(SUM(CASE mm WHEN '10' THEN annual END), 0) m10
     , ISNULL(SUM(CASE mm WHEN '11' THEN annual END), 0) m11
     , ISNULL(SUM(CASE mm WHEN '12' THEN annual END), 0) m12
     , SUM(annual) useday
  FROM (SELECT empnum
             , annual
             , SUBSTRING(smonth, 1, 4) mgyear
             , SUBSTRING(smonth, 5, 2) mm
          FROM annual_copy
         WHERE smonth LIKE '2020%'
        ) a
 GROUP BY empnum, mgyear
;

 


by 한청이 [2021.04.07 08:33:21]

감사합니다! 바로 되네요 ㄷㄷ...

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