[MSSQL] 데이터 좌우 대칭하여 표현하기 - PIVOT 테이블 예제

이번시간에는 MSSQL에서 날짜별로 컬럼을 배치할때 자주쓰이는 PIVOT에 대한 개념을 학습하였다. 

 

-- 문법

SELECT *
  FROM ( 피벗할 쿼리문 ) AS result
 PIVOT ( 그룹합수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값] ... ) AS pivot_result

 

-- 예제 샘플 --

 

-- create 문 
USE [DB이름으로 수정]
GO

/****** Object:  Table [dbo].[EMP]    Script Date: 2021-08-19 오전 10:01:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EMP](
[SEQ] [int] IDENTITY(1,1) NOT NULL,
[EMPNO] [int] NOT NULL,
[ENAME] [nchar](10) NULL,
[JOB] [varchar](40) NULL,
[DEPTNO] [int] NULL,
[MGR] [int] NULL,
[HIREDATE] [datetime] NULL,
[SAL] [int] NULL,
[COMM] [int] NULL,
 CONSTRAINT [PK_EMP] PRIMARY KEY CLUSTERED 
(
[SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO




-- 데이터 insert 
insert into emp values( 7839, 'KING', 'PRESIDENT', 10, null,  CONVERT(CHAR(10), '1991-01-05', 23), 5000, null);

insert into emp values( 7698, 'BLAKE', 'MANAGER', 20, 7839, CONVERT(CHAR(10), '1991-01-05', 23), 2850, null);

insert into emp values( 7782, 'CLARK', 'MANAGER', 30, 7839, CONVERT(CHAR(10), '1999-09-06', 23), 2450, null);

insert into emp values( 7566, 'JONES', 'MANAGER', 20, 7839,  CONVERT(CHAR(10), '2001-02-04', 23), 2975, null);

insert into emp values( 7788, 'SCOTT', 'ANALYST', 10, 7566,  CONVERT(CHAR(10), '2003-06-17', 23), 3000, null);

insert into emp values( 7902, 'FORD', 'ANALYST', 20, 7566,  CONVERT(CHAR(10), '1981-03-12', 23), 3000, null);

insert into emp values( 7369, 'SMITH', 'CLERK', 20, 7902,  CONVERT(CHAR(10), '2007-12-01', 23), 800, null);

insert into emp values( 7499, 'ALLEN', 'SALESMAN', 10, 7698,  CONVERT(CHAR(10), '1981-02-20', 23), 1600, 300);

insert into emp values( 7521, 'WARD', 'SALESMAN', 20, 7698,  CONVERT(CHAR(10), '1981-02-22', 23), 1250, 500);

insert into emp values( 7654, 'MARTIN', 'SALESMAN', 30, 7698,  CONVERT(CHAR(10), '1981-09-28', 23), 1250, 1400);

insert into emp values( 7844, 'TURNER', 'SALESMAN', 20, 7698,  CONVERT(CHAR(10), '1981-08-09', 23), 1500, 0);

insert into emp values( 7876, 'ADAMS', 'CLERK', 30, 7788,  CONVERT(CHAR(10), '2013-07-07', 23), 1100, null);

insert into emp values( 7900, 'JAMES', 'CLERK', 20, 7698,  CONVERT(CHAR(10), '2003-12-1981', 23), 950, null);

insert into emp values( 7934, 'MILLER', 'CLERK', 10, 7782,  CONVERT(CHAR(10), '2003-1-23', 23) , 1300, null);



-- deptno 기준으로 pivot 
SELECT * 
FROM (
SELECT JOB,
DEPTNO
,SAL
FROM EMP
)AS RESULT
PIVOT 
( 
SUM(SAL) FOR DEPTNO IN ([10],[20],[30],[40])
) AS PIVOT_RESULT
ORDER BY JOB


SELECT * FROM 
EMP


DECLARE @COLUMNS NVARCHAR(MAX)
DECLARE @SQL     NVARCHAR(MAX)

SET @COLUMNS = ''

SELECT @COLUMNS = @COLUMNS + '[' + yyyymm + '],'
  FROM (
         SELECT DISTINCT
                CONVERT(NVARCHAR(6), hiredate, 112) AS yyyymm
           FROM emp
          WHERE hiredate BETWEEN '1981-01-01' AND '2010-06-30'
       ) AS months
 ORDER BY yyyymm

SET @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1)

SET @SQL = ' 
    SELECT *
      FROM (
             SELECT empno
                  , job
                  , CONVERT(NVARCHAR(6), hiredate, 112) AS yyyymm
               FROM emp
              WHERE hiredate BETWEEN ''1981-01-01'' AND ''2010-06-30''
            ) AS result
      PIVOT ( 
              COUNT(empno) 
              FOR yyyymm IN (' + @COLUMNS + ') 
            ) AS pivot_result 
'

EXEC(@SQL)

-- DEPTNO
SELECT * 
FROM (
SELECT EMPNO,
JOB
FROM EMP
) AS RESULT
PIVOT (
COUNT(EMPNO)
FOR JOB IN ([PRESIDENT],[MANAGER],[ANALYST],[SALESMAN],[CLERK])
) AS PIVOT_RESULT


SELECT * 
FROM (
SELECT EMPNO, JOB
, CONVERT(NVARCHAR(6), HIREDATE, 112) AS yyyymm
from emp 
where hiredate between '1981-01-01' and '1990-06-30'
) as result
pivot (
count(empno)
for yyyymm in ([198102],[198103],[198108],[198109],[199101])
) as pivot_result