MSSQL 월별 통계 가로 - MSSQL wolbyeol tong-gye galo

테이블1

위와같은 테이블이 있다고 했을시  월별 데이터 건수와 월별금액 합계를 가로로 출력하고싶습니다.

SELECT
COUNT(idx) AS totalcount,

COUNT(CASE WHEN DATEPART(mm, 날짜) = 1 THEN '' ELSE NULL END) AS M01,
SUM(CASE WHEN DATEPART(mm, 날짜) = 1 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 2 THEN '' ELSE NULL END) AS M02,
SUM(CASE WHEN DATEPART(mm, 날짜) = 2 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 3 THEN '' ELSE NULL END) AS M03,
SUM(CASE WHEN DATEPART(mm, 날짜) = 3 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 4 THEN '' ELSE NULL END) AS M04,
SUM(CASE WHEN DATEPART(mm, 날짜) = 4 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 5 THEN '' ELSE NULL END) AS M05,
SUM(CASE WHEN DATEPART(mm, 날짜) = 5 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 6 THEN '' ELSE NULL END) AS M06,
SUM(CASE WHEN DATEPART(mm, 날짜) = 6 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 7 THEN '' ELSE NULL END) AS M07,
SUM(CASE WHEN DATEPART(mm, 날짜) = 7 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 8 THEN '' ELSE NULL END) AS M08,
SUM(CASE WHEN DATEPART(mm, 날짜) = 8 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 9 THEN '' ELSE NULL END) AS M09,
SUM(CASE WHEN DATEPART(mm, 날짜) = 9 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 10 THEN '' ELSE NULL END) AS M10,
SUM(CASE WHEN DATEPART(mm, 날짜) = 10 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 11 THEN '' ELSE NULL END) AS M11,
SUM(CASE WHEN DATEPART(mm, 날짜) = 11 THEN 금액 ELSE 0 END),

COUNT(CASE WHEN DATEPART(mm, 날짜) = 12 THEN '' ELSE NULL END) AS M12,
SUM(CASE WHEN DATEPART(mm, 날짜) = 12 THEN 금액 ELSE 0 END)

From 테이블1

제머리론 위에쿼리밖에 안나오네요..;;

저걸 간소화 한다거나.. 성능이 좋아지게좀 바꿀순 없을까요??

원문 : http://database.sarang.net/?inc=read&aid=4438&criteria=mssql&subcrit=&id=&limit=20&keyword=%B1%DD%BE%D7&page=1

오라클에서 가로로 결과물을 출력시 decode 를 사용하지만, Ms-Sql 에서는 case 를 사용해 가로로 출력할 수 있다.

(테이블)

상환일자    원금   이자  잔액
 ---------------------------
 2008-05-25 10000 1000 121000
 2008-06-25 10000 1000 110000
 2008-07-25 10000 1000 99000
 2008-08-25 10000 1000 88000
 2008-09-25 10000 1000 77000
 2008-10-25 10000 1000 66000
 2008-11-25 10000 1000 55000
 2008-12-25 10000 1000 44000
 2009-01-25 10000 1000 33000
 2009-02-25 10000 1000 22000
 2009-03-25 10000 1000 11000
 2009-04-25 10000 1000 0

(출력)

연도 구분 1월   2월    3월   4월    5월    6월   7월   8월   9월   10월  11월  12월
 ---------------------------------------------------------------------------------------------------
 2008 원금  null  null  null  null  10000  10000 10000 10000 10000 10000 10000 10000
 2008 이자  null  null  null  null   1000   1000  1000  1000  1000  1000  1000  1000
 2008 잔액  null  null  null  null 121000 110000 99000 88000 77000 66000 55000 44000
 2009 원금 10000 10000 10000 10000   null   null  null  null  null  null  null  null
 2009 이자  1000  1000  1000  1000   null   null  null  null  null  null  null  null
 2009 잔액 33000 22000 11000     0   null   null  null  null  null  null  null  null

(쿼리)

select
       year(상환일자) 연도,
       구분,
       sum(case datepart(mm,상환일자) when 1 then 금액 end) [1월],
       sum(case datepart(mm,상환일자) when 2 then 금액 end) [2월],
       sum(case datepart(mm,상환일자) when 3 then 금액 end) [3월],
       sum(case datepart(mm,상환일자) when 4 then 금액 end) [4월],
       sum(case datepart(mm,상환일자) when 5 then 금액 end) [5월],
       sum(case datepart(mm,상환일자) when 6 then 금액 end) [6월],
       sum(case datepart(mm,상환일자) when 7 then 금액 end) [7월],
       sum(case datepart(mm,상환일자) when 8 then 금액 end) [8월],
       sum(case datepart(mm,상환일자) when 9 then 금액 end) [9월],
       sum(case datepart(mm,상환일자) when 10 then 금액 end) [10월],
       sum(case datepart(mm,상환일자) when 11 then 금액 end) [11월],
       sum(case datepart(mm,상환일자) when 12 then 금액 end) [12월]
 from (
         select
              a.상환일자,
              b.구분,
              case b.구분 when '원금' then a.원금 
                          when '이자' then a.이자 
                          when '잔액' then a.잔액 
              end 금액
         from
              test a
             ,(select '원금' 구분 union all select '이자' union all select '잔액') b
      ) t
group by year(상환일자),구분
order by year(상환일자),구분

날짜별형식변환은 이전글쓰기내용중에서 cast,conver 부분을 참고하시면됩니다. 다음링크에서 참고하시기 바랍니다. 링크정보 : sosopro.tistory.com/48

SalesLT.Address테이블의 대한 데이터로 통계쿼리를 살펴보도록하겠습니다. 수정한일자에 대한 통계를 알아볼예정이며 일자별 CountryRegion에 개수에 대해 살펴보도록 하겠습니다. 

1. 년단위 통계
년별로 도시주소가 수정된 개수에 대한 통계값입니다. convert함수를 이용하여 년단위로 출력하게합니다. 

쿼리결과입니다. 

2. 분기별 통계
분기별 통계시에는 quarter를 사용하면 분기별(1~4)로 구분되어서 출력이 가능합니다.

쿼리결과

3. 월단위 통계
DATEPART부분을 mm으로 하게 실행하면됩니다.

쿼리결과 

4. 주간별통계
DATEPART부분을 ww으로 하게 실행하면됩니다. 년도별로 구분을 하기위해서 DATEPART(YYYY,ModifiedDate)을 두었고 해당 부분뒤에 DATEPART(ww,ModifiedDate),CountryRegion으로쿼리문을실행해보겠습니다.

쿼리결과입니다

5. 일단위 통계
일단위 통계입니다. DD를 입력한후 group by를 해주면 일자별 통계가 출력가능합니다. 

쿼리결과입니다. 테스트일자가 1일밖에 없어서 1일에 데이터만 출력됩니다.

5. DATEPART 정보
DATEPART부분의 인수에 해당하는 정보입니다. 적절하게 사용하셔서 통계자료를 만드시기 바랍니다. 

참고,출처 : docs.microsoft.com/ko-kr/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15