Sunday, November 21, 2010

Pivoting tables in SQL

Sometimes we need to pivot a table in SQL.

Imagine a table (called test1) with monthly sales numbers

yearmonthSalesPersonSales $
20096a6.1
20096b6.2
20097a7.1
20098a8.1
20098b8.2
20099b9.2
20101a1.1
20102b2.1
20103b3.1
20104a4.1
20104b4.2
20105a5.1

You can create and populate your test table using this SQL (works for Sybase and MS SQL server):
/***********************************************************/
if exists (select 1 from sysobjects where name='test1' and type='U')
   begin
     drop table test1
   end
go

create table test1
(
 year   int,
 month  int,
 sales_team char(4),
 sales  float
)

insert test1 select 2009, 6, "a", 6.1
insert test1 select 2009, 6, "b", 6.2
insert test1 select 2009, 7, "a", 7.1
insert test1 select 2009, 8, "a", 8.1
insert test1 select 2009, 8, "b", 8.2
insert test1 select 2009, 9, "b", 9.1
insert test1 select 2010, 1, "a", 1.1
insert test1 select 2010, 2, "b", 2.1
insert test1 select 2010, 3, "b", 3.1
insert test1 select 2010, 4, "a", 4.1
insert test1 select 2010, 4, "b", 4.2
insert test1 select 2010, 5, "b", 5.1
go
/*******************************************************************/

Let's say we need to sum-up the sales numbers by months for each year in one SQL statement. Below are the 2 ways of achieving that using one SQL statement.

The recommended way (and enforced by Microsoft in their SQL Server) is to use the case statement with GROUP BY as follows:

/********************************************************/
select 
 year,
 m_1_sls = sum(case
                      when month=1  then sales
                      else null
                     end),
 m_2_sls = sum(case
                      when month=2  then sales
                      else null
                     end),
 m_3_sls = sum(case
                      when month=3  then sales
                      else null
                     end),
 m_4_sls = sum(case
                      when month=4  then sales
                      else null
                     end),
 m_5_sls = sum(case
                      when month=5  then sales
                      else null
                     end),
 m_6_sls = sum(case
                      when month=6  then sales
                      else null
                     end),
 m_7_sls = sum(case
                      when month=7  then sales
                      else null
                     end),
 m_8_sls = sum(case
                      when month=8  then sales
                      else null
                     end),
 m_9_sls = sum(case
                      when month=9  then sales
                      else null
                     end),
 m_10_sls = sum(case
                       when month=10  then sales
                       else null
                      end),
 m_11_sls = sum(case
                       when month=11  then sales
                       else null
                      end),
 m_12_sls = sum(case
                       when month=12  then sales
                       else null
                      end)
from test1
group by year
/****************************************************/

There is also less conventional way of achieving pretty much the same result - using "characteristic functions" as suggested by Anatoly Abramovich, Eugene Birger, and David Rozenshtein

/***********************************************************/
SELECT year, 
       m_1_sls=sum(
                          (1- ABS( SIGN( ISNULL( 1 - month, 1)))) * sales
                        ),
       m_2_sls=sum(
                          (1- ABS( SIGN( ISNULL( 2 - month, 1)))) * sales
                        ),
       m_3_sls=sum(
                          (1- ABS( SIGN( ISNULL( 3 - month, 1)))) * sales
                        ),
       m_4_sls=sum(
                          (1- ABS( SIGN( ISNULL( 4 - month, 1)))) * sales
                        ),
       m_5_sls=sum(
                          (1- ABS( SIGN( ISNULL( 5 - month, 1)))) * sales
                        ),
       m_6_sls=sum(
                          (1- ABS( SIGN( ISNULL( 6 - month, 1)))) * sales
                        ),
       m_7_sls=sum(
                          (1- ABS( SIGN( ISNULL( 7 - month, 1)))) * sales
                        ),
       m_8_sls=sum(
                          (1- ABS( SIGN( ISNULL( 8 - month, 1)))) * sales
                        ),
       m_9_sls=sum(
                          (1- ABS( SIGN( ISNULL( 9 - month, 1)))) * sales
                        ),
       m_10_sls=sum(
                          (1- ABS( SIGN( ISNULL( 10 - month, 1)))) * sales
                        ),
       m_11_sls=sum(
                          (1- ABS( SIGN( ISNULL( 11 - month, 1)))) * sales
                        ),
       m_12_sls=sum(
                          (1- ABS( SIGN( ISNULL( 12 - month, 1)))) * sales
                        )

from test1
group by year
/****************************************************/
The result of these SQLs will be:
yearm_1_slsm_2_slsm_3_slsm_4_slsm_5_slsm_6_slsm_7_slsm_8_slsm_9_slsm_10_slsm_11_slsm_12_sls
2009nullnullnullnullnull12.37.116.39.1nullnullnull
20101.12.13.18.35.1nullnullnullnullnullnullnull

1 comment:

  1. Pivoting tables is still new to me. Thanks a lot for showing how to do it.
    Sql training

    ReplyDelete