Pivot

아래와 같이 년도별 잔고를 조회하면 아래와 같이 결과가 나오는 조회문이 있다고 보자. 

select Year, Balance from Account;

Year	Balance
2010	1000
2011	1200
2012	1100
2013	1400

그런데 왠지 마음에 들지 않는다, 위에서 아래로 연도별로 잔고가 나오는 결과를 왼쪽에서 부터 오른쪽으로 년도를 컬럼으로 하고 잔고를 그 년도에 값으로 표현하고 싶다.

2010	2011	2012	2013
1000	1200	1100	1400ㅣ

이럴 때 어떻게 하면 좋을까? 정답은 Pivot에 있다. 

Pivot은 A, B, C, D라는 컬럼이 있는데, 여기서 A라는 컬럼에 있는 값들이 다 다를때, 이 A 컬럼의 값을 조회 결과의 컬럼명이 되게 하고 나머지 B, C, D 컬럼의 값을 이 A컬럼의 값이 되게 하도록 해주는 즉, 결과값의 출력 형태를 바꾸어 주는 연산자이다.

 

Pivot을 사용하기 방법은 

 

먼저 Pivot을 하기 위해 조회문을 작성해준다.

두번째 임시 결과 셋을 파생테이블이나 CTE(Common Table Expresssion)을 이용해서 만든다. 

세번째 Pivot 연사자를 적용한다.

 

아주 간단한 예로 한번 해보자.

 

create table Accounts (
    AccountNo nvarchar(100),
    TradeDt date, 
    Balance int 
); 

insert into Accounts (AccountNo, TradeDt, Balance) 
values (123, '2010-01-01', 1000) 
        ,(456, '2010-12-31', 1050)
        ,(123, '2011-01-01', 1200) 
        ,(123, '2012-01-01', 1100) 
        ,(123, '2013-01-01', 1400)
;

with tempCte (yy, balance) as ( 
     select year(TradeDt) as yy, 
             balance    
     from Accounts 
) select * from tempCte as t 
  pivot ( 
      sum(balance) 
      for yy in ( 
      [2010], 
      [2011], 
      [2012], 
      [2013] 
    ) 
 ) as p

조회 결과

여기서 추가로 CTE에 컬럼 AccountNo를 추가해 보겠다. 그럼 결과는 어떨까. 이 추가된 컬럼은 자동으로 그룹으로 묶어서 별도에 컬럼으로 보여준다.

 

with tempCte (yy, balance, AccountN0) as ( 
    select year(TradeDt) as yy, 
            balance,
            AccountNo
    from Accounts 
) select * from tempCte as t 
pivot ( 
    sum(balance) 
    for yy in ( 
    [2010], 
    [2011], 
    [2012], 
    [2013] 
    ) 
) as p

조회결과

지금까지 in 다음에 컬럼명을 나열했다. 뭔가 조금 불편하다. 매번 바뀔 때마다 한땀한땀 손으로 컬럼명들을 추가하거나 바꾸어야 하는 등 불편했다. 조금 더 편하게 해보자. 

 

먼저 컬럼명을 자동으로 만드는 방법을 알아보자.

 

여기에 사용될 함수는 QouteName()과 Left()이다. 

 

QuoteName()은 컬럼명 양옆에 대괄호를 씌어준다. [column_name] 

 

Left()는 문자를 잘라주는 역할을 한다. 

 

Declare @columns nvarchar(max) = '';

Select @columns += QUOTENAME(year(TradeDt)) + ',' 
from Accounts
group by year(TradeDt)
order by year(TradeDt)
asc;

set @columns = Left(@columns, Len(@columns) -1);

select @columns;

 

자동으로 만든 컬럼

이제 동적으로 컬럼명을 만들 수 있게 되었으니 sp_excutesql을 이용해서 다시 Pivot을 이용한 결과를 만들어 보자

 

Declare @columns nvarchar(max) = '',
        @sql nvarchar(max);

Select @columns += QUOTENAME(year(TradeDt)) + ',' 
from Accounts
group by year(TradeDt)
order by year(TradeDt)
asc;

set @columns = Left(@columns, Len(@columns) - 1);

set @sql = '
    with tempCte (yy, balance) as (  
        select year(TradeDt) as yy,  
                balance
        from Accounts  
     ) select * from tempCte as t  
         pivot (  
             sum(balance)  
             for yy in (' + @columns + ')  
          ) as p;';

EXEC sp_executesql @sql;