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 |
