MSSQL 동적 PIVOT 쿼리

MSSQL 환경에서 데이터 행과 열을 전환해야하는 상황...

앞으로 주어질 데이터가 몇행이고 값이 무엇일지 아무도 모른다고 해서 열심히 구글링을 했는데 동적 쿼리로 해결될 수 있다는 결론이 나옴!!

 

동적쿼리는 임시 테이블, 변수 등등 뭔가 조건이 많아서 이해하기 힘들었는데 다음 블로그를 찾아 수월하게 진행할 수 있었다

 

https://maelife.tistory.com/173

MSSQL PIVOT 여러컬럼 동적으로 수행하기

 

 

이제 나의 분석 START

---

목적

주어진 데이터는 다음과 같음

내가 만든 데이터이며 숫자에 특별한 의미같은 것 없습니다

이걸 이렇게 바꾸고 싶음

히히 결과 미리 보여주기

 

 

데이터 만들기

주석으로 말했지만, 임의로 내가 만든 데이터이고, 앞으로 데이터를 엑셀로 불러올 예정이라 코드로 작성하지 않은 부분이지만 혹시 몰라 따라할 분들을 위해 적자면 다음과 같음

-- 테이블 만들기
CREATE TABLE YEARLY_RATE
(
	 Year	FLOAT(10)
	,Ratio	FLOAT(10)
)

-- 만든 테이블에 내용 넣기
INSERT INTO YEARLY_RATE(Year, Ratio) VALUES(2019, 0.4)
INSERT INTO YEARLY_RATE(Year, Ratio) VALUES(2020, 0.3)
INSERT INTO YEARLY_RATE(Year, Ratio) VALUES(2021, 0.9)
INSERT INTO YEARLY_RATE(Year, Ratio) VALUES(2022, 0.8)
INSERT INTO YEARLY_RATE(Year, Ratio) VALUES(2023, 0.5)

-- 결과 보기
-- 여유롭게 1000개 보는걸로 잡았는데 데이터 개수가 이보다 적으면 개수대로 출력됨
SELECT TOP (1000) *
  FROM YEARLY_RATE

 

 

 

임시 항목을 생성함

 DECLARE @PIVOT_COL VARCHAR(MAX)
 DECLARE @PIVOT_QUERY VARCHAR(MAX)

각 문장을 설명하자면 

- 원래 ROW로 주어졌던 변수(나에겐 Year이 될 것이다)를 COLUMN으로 만들어버리기 위해서 PIVOT_COL이란 임시 변수에 호출

- 작성할 PIVOT 쿼리를 PIVOT_QUERY란 항목에 넣기 위해 임시 변수로 호출

 

문자로 CONCAT하여 나중에 분리시킬것이라 VARCHAR로 지정하는 것이고, 길이는 자유자재로 활용할 수 있게 MAX로 지정

 

 

 

ROW로 주어진 변수 CONCAT하기

 SELECT @PIVOT_COL = @PIVOT_COL + '[' + CONVERT(VARCHAR(10), Year) + '],'
 FROM (
		SELECT DISTINCT Year
		FROM YEARLY_RATE
	  ) AS COLS

-- COLUMN 총 값 중 맨 뒤 , 항목 삭제
SET @PIVOT_COL = LEFT( @PIVOT_COL, LEN(@PIVOT_COL) - 1 )

COLUMN으로 변경할 변수들의 값을 "[값1], [값2], ..." 이렇게 붙여버리려는 것 

Year가 Float로 주어졌기에 CONVERT 함수를 썼다( 엑셀로 불러올거라.. 데이터를 쿼리로 짜면 처음부터 VARCHAR로 지정이 가능한데 난 불가하므로 이 과정이 필요했다)

 

내용을 말로 설명하자면,

임시변수 PIVOT_COL을 만들건데, '[', '],' 사이에 Year 값을 넣자.

이 Year이 어디서 오는지 설명해줄건데, 이전에 만들었던 데이터에 Year이란 변수를 중복제거(DISTINCT)해서 가져오면 됨

아까 '],'를 붙여버려서 마지막에도 , 가 들어가서 제외해야하는데, 왼쪽에서부터(LEFT) 총 길이(LEN) -1 을 해서 맨 마지막 항목을 제거하자

 

 

 

동적 PIVOT 쿼리 작성하기

SET @PIVOT_QUERY =
		'
		SELECT '+@PIVOT_COL+'
        	INTO PIVOT_RESULT
		FROM (
				SELECT Year, Ratio
				FROM YEARLY_RATE
				) AS TEMP
		PIVOT (SUM(Ratio) FOR Year IN ('+@PIVOT_COL+') ) AS PVT
		'

이부분이 제일 심적 거리감이 멀었음... 근데 해보니 되네...? 다시 한번 참고했던 블로그 주인님께 무한감사

 

말로 설명하자면,

임시변수 PIVOT_QUERY를 지정해줄건데, 이걸로 PIVOT 실행하게 할거임

아까 만들었던 PIVOT_COL을 각 변수들로 분리해서 선택해줄건데,

일단 PIVOT할 원래 테이블을 FROM으로 불러주고,

합친 Ratio 값을 테이블 내용으로, PIVOT_COL 안에 있는 값들을 Year로 설정해서 TABLE을 PIVOT해버릴것임

 

INTO를 넣은 이유는 PIVOT 결과를 나중에 활용할 것이기 때문... 나중에 FROM PIVOT_RESULT 하면 피봇 결과값 활용이 가능하다

 

...말로 설명하는게 뭔가 더 어색한거 같은데 ㅋㅋㅋㅋ 일단 이렇게하고 다음 과정으로 실행

 

 

실행

EXEC(@PIVOT_QUERY)
PRINT @PIVOT_QUERY

 

 

그럼 짠!

원하는 테이블이 나왔다 후후후후

 

 

 

 

총 코드 정리

 -- 임시 항목 생성
 DECLARE @PIVOT_COL VARCHAR(MAX)
 DECLARE @PIVOT_QUERY VARCHAR(MAX)

 -- 초기 값 설정
 SET @PIVOT_COL = ''
 SET @PIVOT_QUERY = ''

 -- Year 총 값 CONCAT
 SELECT @PIVOT_COL = @PIVOT_COL + '[' + CONVERT(VARCHAR(10), Year) + '],'
 FROM (
		SELECT DISTINCT Year
		FROM YEARLY_RATE
	  ) AS COLS

-- COLUMN 총 값 중 맨 뒤 , 항목 삭제
SET @PIVOT_COL = LEFT( @PIVOT_COL, LEN(@PIVOT_COL) - 1 )

-- 값 잘 출력되었는지 확인
-- PRINT(@PIVOT_COL)

-- 동적 PIVOT 쿼리
SET @PIVOT_QUERY =
		'
		SELECT '+@PIVOT_COL+'
        	INTO PIVOT_RESULT
		FROM (
				SELECT Year, Ratio
				FROM YEARLY_RATE
				) AS TEMP
		PIVOT (SUM(Ratio) FOR Year IN ('+@PIVOT_COL+') ) AS PVT
		'

-- 실행
EXEC(@PIVOT_QUERY)
PRINT @PIVOT_QUERY

 

 

혹여나 수정해야할 내용이 있으면 언제든지 알려주세요~!

감사합니다