MSSQL 가변 컬럼 - MSSQL gabyeon keolleom

이번 포스팅에서는 앞선 두 포스팅에서 배웠던

문자열 합치기 : STUFF, FOR XML PATH

문자열 구분자 추가 : QUOTENAME

기법들을 활용해 동적 피벗테이블을 만들어보도록 하겠습니다.

 동적 피벗 테이블 만들기 예제 

테이블 DEPT, EMP를 이용해서 연도별(DT) 부서 입사 현황을 조회한다

(회사코드(COMPANY):0327, 사업장코드(BIZAREA):1000 데이터로 한정함)

1. PIVOT을 이용해 년도를 칼럼으로 표시

2. 동적컬럼 생성 시 STUFF, QUOTENAME 함수를 이용하여 입사자가 있는 년도만 표시

결과 화면 예시

동적 피벗 만들기

DECLARE @COL AS NVARCHAR(MAX) --변수선언
DECLARE @QUERY AS NVARCHAR(MAX)

--변수에 쿼리결과 저장--
SELECT @COL = STUFF((SELECT ',' + QUOTENAME(SUBSTRING(DT,0,5))
FROM EMP
WHERE
BIZAREA = '1000' AND
COMPANY = '0327' AND
DT IS NOT NULL   
GROUP BY SUBSTRING(DT,0,5)
ORDER BY SUBSTRING(DT,0,5)
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)') 
,1,1,'')

 --동적피벗만들기--
SET @QUERY =
'SELECT * FROM
(
SELECT
A.CD_DEPT,
A.NM_DEPT AS 부서명,
SUBSTRING(B.DT,0,5) AS DT
FROM DEPT A
LEFT OUTER JOIN EMP B ON A.COMPANY = B.COMPANY AND 
A.DEPT = B.DEPT AND A.BIZAREA = B.BIZAREA
WHERE
A.BIZAREA = ''1000'' AND
A.COMPANY = ''0327'' AND
B.DT IS NOT NULL
)Q
PIVOT
(
COUNT(DEPT) FOR DT IN ('+ @COL +')
)P'
EXECUTE(@QUERY);

[MSSQL] PIVOT, UNPIVOT (행렬 변환)

PIVOT / UNPIVOT

PIVOT 과 UNPIVOT

-- PIVOT

SELECT 컬럼명 FROM
테이블명
PIVOT([집계 함수]([집계 컬럼명]) FOR [대상 컬럼명] IN ([컬럼이 될 값])) AS 피벗테이블명

-- UNPIVOT

SELECT 컬럼명
FROM 테이블명
UNPIVOT([집계될 데이터의 컬럼명] FOR [UNPIVOT할 컬럼 데이터의 컬럼명] IN ([UNPIVOT할 컬럼명])) AS 언피벗테이블명

일반적으로 PIVOT과 UNPIVOT의 대상 컬럼과 컬럼이 될 값을 지정해줘야 한다.

그런데 만약 컬럼명이나 행의 값들이 동적이라면?

이를 고려하여 동적 PIVOT / UNPIVOT 쿼리를 작성해보자

동적 PIVOT

DECLARE @Query VARCHAR(MAX);

SET @Query = '';
SELECT @Query = @Query + '[' +  반 + '],'
FROM (SELECT DISTINCT 반 FROM TABLE_A) A

SET @Query = LEFT(@Query, LEN(@Query)-1) -- [1반],[2반]

-- 실행
EXEC ('SELECT * FROM TABLE_A PIVOT(SUM(점수) FOR 반 IN (' + @Query + ')) AS PVT')

동적 UNPIVOT

DECLARE @Query VARCHAR(MAX);
DECLARE @strColumn VARCHAR(MAX);
DECLARE @keyColumn VARCHAR(100) = '학년';

-- 컬럼명 추출
SELECT name INTO #TEMP
FROM sys.all_columns
WHERE object_id = object_id('TABLE_A')
AND name <> @keyCloumn

-- 컬럼명 한줄로 처리
SET @strColumn = STUFF((SELECT '],[' + name FROM #TEMP FOR XML PATH('')) + ']', 1, 1, '');
SET @strColumn = RIGHT(@strColumn, LEN(@strColumn)-1); -- [1반],[2반]

-- UNPIVOT 쿼리 작성
SET @Query = 'SELECT * FROM TABLE_A UNPIVOT([점수] FOR 반 IN (' + @strColumn + ')) AS UNPVT'

-- 실행
EXEC (@Query)

간단할 수 있는 정렬의 기준이 되는 조건을 변경하는 쿼리에 대해서 정리를 하겠습니다.

우선 처음에 확인을 했던 쿼리의 경우 ORDER BY에 정수(숫자)를 넣어서 아래와 같이 쿼리를 수행하면, 조회하는 컬럼의 순번에 해당하는 컬럼을 기준으로 정렬을 하는것을 확인할 수 있었습니다.

<사진 1> 1번째 컬럼 기준으로 정렬 - (사원번호 순서로 정렬 )

<사진 2> 2번째 컬럼 기준으로 정렬 - (사원명 순서로 정렬 )

하지만 Case문에 숫자값을 경우에 따라 반환하게 하여도, 원하는 컬럼을 기준으로 정렬하게 할 수 없었습니다.

MS SQL 문서 중 아래의 링크에 있는 문서를 참고하여 처리를 하였습니다.

https://docs.microsoft.com/ko-kr/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017

ORDER BY 절(Transact-SQL) - SQL Server

SELECT - ORDER BY 절(Transact-SQL)SELECT - ORDER BY Clause (Transact-SQL) 이 문서의 내용 --> 적용 대상: SQL Server(2008부터) Azure SQL Database Azure SQL Data Warehouse 병렬 데이터 웨어하우스 APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse

docs.microsoft.com

쿼리를 작성 해 보니 임시변수인 @temp값에 따라서 사원번호 순으로 정렬하거나 사원명 순으로 정렬할 수 있는 쿼리를  아래와 같이 작성하였습니다.

<사진 3> ORDER BY에 원하는 컬럼을 조건에 따라 골라쓰는 쿼리

<사진 4> temp값이 1일 때 -> 사원번호 순으로 정렬

<사진 5> temp값이 2일 때 -> 사원명 순으로 정렬

위와 같이 작성을 하니 @temp 값에 따라서 사원번호 순으로 정렬하거나 사원명 순으로 정렬할 수 있게 되었습니다.