[MSSQL] ROW를 COLUMN으로 변환해서 통계용 테이블 만들기 (PIVOT 없이)

접속 로그나 통계 데이터를 다루다 보면, 아래처럼 행(ROW) 형태로 쌓인 데이터를 열(COLUMN) 형태의 통계 표로 바꾸고 싶은 경우가 자주 있습니다.

문제 상황 – 로그가 ROW 형태로 쌓이는 경우

예를 들어 방문 로그 테이블이 다음과 같은 형태라고 해보겠습니다.

순번날짜menuid합계
12009-04-16coupon8
22009-04-16hot_moneylife1
32009-04-16main34
42009-04-16payinfo8
52009-04-16public11
62009-04-16sitemap5
72009-04-15coupon11
82009-04-15hot_chosun5
92009-04-15hot_moneylife18
102009-04-15main82
112009-04-15payinfo8
122009-04-15public12
132009-04-15sitemap4
142009-04-14coupon17
152009-04-14hot_chosun4
162009-04-14hot_moneylife24
172009-04-14main96
182009-04-14payinfo12
192009-04-14public14
202009-04-14sitemap5

이 데이터를 통계 화면이나 리포트에서 보기 좋게 만들기 위해서는 날짜별로 메뉴를 가로축으로 펼쳐서, 아래와 같은 형태로 출력하고 싶을 수 있습니다.

순번날짜메인접속할인쿠폰공공정보이용요금안내사이트맵**2010**7218
12009-04-16348118510
22009-04-1582111284185
32009-04-14961714125244

딱 봐도 PIVOT이 떠오르지만, 컬럼 이름을 세밀하게 제어하고 싶거나, PIVOT을 잘 쓰지 않는 환경이라면 CASE WHEN + SUM 조합만으로도 충분히 해결할 수 있습니다.


핵심 아이디어 – SUM(CASE WHEN … THEN 1 ELSE 0 END)

원리는 간단합니다. 날짜별로 GROUP BY를 하고, 각 menuid 값에 대해 SUM(CASE WHEN menuid = '...' THEN 1 ELSE 0 END) 형태의 컬럼을 만들어 주면 됩니다.

실제 예시는 다음과 같습니다.


SELECT 
    CONVERT(varchar(10), visitDate, 120) AS 날짜,

    SUM(CASE WHEN menuid = 'main'         THEN 1 ELSE 0 END) AS 메인접속,
    SUM(CASE WHEN menuid = 'coupon'       THEN 1 ELSE 0 END) AS 할인쿠폰,
    SUM(CASE WHEN menuid = 'public'       THEN 1 ELSE 0 END) AS 공공정보,
    SUM(CASE WHEN menuid = 'payinfo'      THEN 1 ELSE 0 END) AS 이용요금안내,
    SUM(CASE WHEN menuid = 'sitemap'      THEN 1 ELSE 0 END) AS 사이트맵,
    SUM(CASE WHEN menuid = 'hot_moneylife' THEN 1 ELSE 0 END) AS '**2010',
    SUM(CASE WHEN menuid = 'hot_chosun'   THEN 1 ELSE 0 END) AS '**7218'

FROM TBL_VISITLOG
GROUP BY CONVERT(varchar(10), visitDate, 120)
ORDER BY 날짜;

이렇게만 해주면, 같은 날짜에 같은 menuid를 가진 행(ROW)들이 각각의 컬럼(COLUMN) 합계로 정리되면서 통계용 피벗 테이블이 만들어집니다.


쿼리 구조 설명

  • CONVERT(varchar(10), visitDate, 120) → visitDate를 YYYY-MM-DD 형식 문자열로 변환해서 날짜별로 묶기 위함입니다.
  • SUM(CASE WHEN menuid = 'main' THEN 1 ELSE 0 END) → menuid가 ‘main’인 행만 1로 계산해서 합산 → 그 날짜의 메인접속 수가 됩니다.
  • GROUP BY 날짜 → 같은 날짜끼리 묶어서 행 1줄에 통계로 표현합니다.
  • ORDER BY 날짜 → 날짜 순으로 정렬하여 통계 리포트처럼 출력.

PIVOT 대신 CASE WHEN을 쓰는 장점

  • 쿼리 구조가 직관적이고 디버깅이 쉽습니다.
  • 컬럼별 별칭(한글 컬럼명 등)을 자유롭게 붙이기 좋습니다.
  • PIVOT 문법에 익숙하지 않아도 기본 집계 함수만 알면 구현 가능합니다.
  • 조건을 더 복잡하게 줄 때도 CASE WHEN 안에 로직을 넣기 쉽습니다.

물론, 컬럼 수가 매우 많거나 동적으로 변한다면 PIVOT을 쓰는 편이 더 나을 수 있지만, 메뉴ID처럼 종류가 고정된 통계라면 위와 같은 방식이 가장 단순하고 관리하기 편했습니다.


마무리 요약

  • ROW → COLUMN 변환은 꼭 PIVOT이 아니어도 SUM(CASE WHEN ...) 으로 구현 가능하다.
  • 날짜별, 카테고리별 등 통계형 리포트에 자주 쓰이는 패턴이다.
  • 고정된 코드값(메뉴ID, 상태값 등)을 가로로 펼치고 싶을 때 특히 유용하다.

예전 프로젝트에서 고민 끝에 정리했던 패턴인데, 지금도 통계 쿼리 작성할 때 자주 사용하는 방식이라 이렇게 기록해 둡니다.