Mssql 세로를 가로로 동적 - mssql seloleul galolo dongjeog

피벗테이블이란?

테이블을 조회한 데이터를 특정 데이터 칼럼으로 사용, 요약된 결과를 만들어 표시하는 것입니다. 사용자 입장에서 데이터를 좀 더 쉽게 볼 수 있도록 출력 형태를 가공할 때 사용합니다.

예제

피벗테이블을 활용하여 세로로 되어있는 칼럼을 가로로 바꿔보는 예제 (행을 열로 변환)

MM_TEST 테이블안에는 위와 같이 DT(날짜), QT(수량)의 데이터가 10만 개가 있습니다.

위 테이블의 월별 합계 수량을 가로로 나타내시오

해결방법

1. 테이블의 월별 합계 수량을 Select 합니다.

DECLARE @DT_FROM NVARCHAR(6) = '200802'
DECLARE @DT_TO NVARCHAR(6) = '200904'

SELECT
MAX(DT) AS DT,
SUM(QT) AS QT
FROM MM_TEST
WHERE DT BETWEEN @DT_FROM AND @DT_TO
GROUP BY DT
ORDER BY DT

2. Select한 쿼리문을 피벗테이블을 활용하여 데이터를 가공합니다. (행, 열 전환)

DECLARE @DT_FROM NVARCHAR(6) = '200802'
DECLARE @DT_TO NVARCHAR(6) = '200904'

SELECT * FROM (
SELECT
MAX(DT) AS DT,
SUM(QT) AS QT
FROM MM_TEST
WHERE DT BETWEEN @DT_FROM AND @DT_TO
GROUP BY DT
)Q
PIVOT (
SUM(QT) FOR DT IN ([200802],[200803],[200804],[200805],[200806],[200807],[200808],[200809],[200810],[200811],[200812],[200901],[200902],[200903],[200904])
)P

3. 정적 피벗테이블을 동적 피벗테이블로 전환합니다.

DECLARE @DT_FROM NVARCHAR(6) = '200802'; --첫번째칼럼
DECLARE @DT_TO NVARCHAR(6) = '200904'; --마지막컬럼
DECLARE @DT_NO NVARCHAR(6); --칼럼(하나)
DECLARE @DT_LAST NVARCHAR(300); --합쳐진 칼럼(문자열)
SET @DT_LAST = '' --초기화

DECLARE MYCUR CURSOR FOR --커서 선언

SELECT A.DT
   FROM (SELECT DT FROM MM_TEST WHERE DT BETWEEN @DT_FROM AND @DT_TO GROUP BY DT)A ORDER BY DT 

OPEN MYCUR
FETCH NEXT FROM MYCUR INTO @DT_NO

WHILE(@@FETCH_STATUS=0) --반복문
BEGIN

SET @DT_LAST = @DT_LAST + '['+@DT_NO+'],' --칼럼 합치기

FETCH NEXT FROM MYCUR INTO @DT_NO

END
CLOSE MYCUR
DEALLOCATE MYCUR --반복문 종료

SET @DT_LAST = LEFT(@DT_LAST, LEN(@DT_LAST)-1) --마지막 ,제거

EXEC('
SELECT * FROM(
SELECT
DT,
SUM(QT) AS QT 
FROM MM_TEST
WHERE DT BETWEEN '+@DT_FROM+' AND '+@DT_TO+
' GROUP BY DT
)Q
PIVOT (
SUM(QT) FOR DT IN ('+ @DT_LAST +')
)AS P') --문자열 쿼리 실행
GO

피벗테이블안에는 동적으로 파라미터를 받을 수 없습니다. 그러므로 피벗테이블 쿼리를 문자열로 만들어 그 문자열을 EXEC 시켜주는 방식으로 동적 피벗테이블을 구현합니다.

최종 결과

[MS_SQL] 반복문(WHILE)사용법 & 예제

[MS_SQL] SET NOCOUNT 정의와 사용법 (프로시저 성능 향상)

[MS_SQL] STUFF와 FOR XML PATH를 활용한 문자열 합치기

[MS_SQL] 동적 피벗 테이블 만들기

[MS_SQL] 문자열 구분자 추가 (QUOTENAME) 함수 사용법 & 예제

by 마농 [2021.05.17 10:05:47]

쿼리 자체 만으로는 동적 컬럼 기능은 없습니다.
동적 컬럼 구현하려며 동적 쿼리 이용해야 합니다.
 

SELECT MIN(CASE id WHEN 'A' THEN val END) A
     , MIN(CASE id WHEN 'B' THEN val END) B
     , MIN(CASE id WHEN 'C' THEN val END) C
     , MIN(CASE id WHEN 'D' THEN val END) D
  FROM t
;

 select * into #Temp

컬럼 col1, col2를 그룹 짓어서 날짜를 가로로 바꿔주려면.. 

--> 이런 쿼리가 나오는데 col3이 가변적이라면 동적쿼리를 이용해야 한다..