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

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

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

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

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

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

2. 핵심 아이디어 – 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) 합계로 정리되면서 통계용 피벗 테이블이 만들어집니다.

3. 쿼리 구조 설명

• 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 날짜 → 날짜 순으로 정렬하여 통계 리포트처럼 출력.

4. PIVOT 대신 CASE WHEN을 쓰는 장점

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

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

5. 마무리 요약

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

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