Pivoting tables in SQL

Sometimes we need to pivot a table in SQL.

Imagine a table (called test1) with monthly sales numbers

year	month	SalesPerson	Sales $
2009	6	a	6.1
2009	6	b	6.2
2009	7	a	7.1
2009	8	a	8.1
2009	8	b	8.2
2009	9	b	9.2
2010	1	a	1.1
2010	2	b	2.1
2010	3	b	3.1
2010	4	a	4.1
2010	4	b	4.2
2010	5	a	5.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:

year

m_1_sls

m_2_sls

m_3_sls

m_4_sls

m_5_sls

m_6_sls

m_7_sls

m_8_sls

m_9_sls

m_10_sls

m_11_sls

m_12_sls

2009

null

null

null

null

null

12.3

7.1

16.3

9.1

null

null

null

2010

1.1

2.1

3.1

8.3

5.1

null

null

null

null

null

null

null