into this:
I usually use the old “case when” for this but being 2010, I’d prefer to use pivot statement. We can use pivot for 1 value column, but unfortunately we can’t use it for 2 value columns. Meaning, in the above source table, if we have sales amount only, or profit amount only, we can use pivot.
Using “case when” it’s like this:
select product,
sum(case when trans_type = ‘type1’ then sales_amount end) as sales_type1,
sum(case when trans_type = ‘type2’ then sales_amount end) as sales_type2,
sum(case when trans_type = ‘type1’ then profit_amount end) as profit_type1,
sum(case when trans_type = ‘type2’ then profit_amount end) as profit_type2
from source_table
group by product
Using pivot it’s like this:
select * from source_table
pivot
( sum(sales_amount)
for trans_type in (type1, type2)
) as p
But how about the profit_amount column? It needs to be pivoted for type1 and type2 too. As I said, you can’t put 2 measures in pivot, like this:
select * from source_table
pivot
( sum(sales_amount)
for trans_type in (type1, type2)
) as p1
pivot
( sum(profit_amount)
for trans_type in (type1, type2)
) as p2
We can’t specify type1 twice like above, because it means we will have 2 columns with the name = [type1]. When we run the above pivot statement SQL Server gives these 4 errors:
Msg 207, Level 16, State 1, Line 11
Invalid column name ‘trans_type’.
Msg 265, Level 16, State 1, Line 11
The column name “type1” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 11
The column name “type2” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 12
The column ‘type1’ was specified multiple times for ‘p2’.
SQL Server 2008 Books Online gives this syntax for “FROM” clause:
[ FROM { <table_source> } [ ,…n ] ]
<table_source> ::=
{ …
| <pivoted_table>
…
}
<pivoted_table> ::=
table_source
PIVOT <pivot_clause>
table_alias
<pivot_clause> ::=
( aggregate_function ( value_column [ [ , ]…n ])
FOR pivot_column
IN ( <column_list> )
)
<column_list> ::=
column_name [ ,…n]
Notice that on the value_column part there is “, n” meaning that we can specify more than 1 value column. But if we try it:
select * from source_table
pivot
( sum(sales_amount, profit_amount)
for trans_type in (type1, type2)
) as p
But SQL Server gives this error message:
Msg 8144, Level 16, State 2, Line 1
Procedure or function sum has too many arguments specified.
Aaarrggghhh…
So what’s the solution?
We need to provide 2 trans type columns with different value, like this:
That is done by selecting the trans type column twice,
adding “p” prefix on the second time:
select product, trans_type as trans_type1, ‘p’ + trans_type as trans_type2, sales_amount, profit_amount
from source_table
Using the above as a derived table, we pivot it twice, like
this:
select * from
( select product, trans_type as trans_type1, ‘p’ + trans_type as trans_type2,
sales_amount, profit_amount
from source_table
) s
pivot
( sum(sales_amount)
for trans_type1 in (type1, type2)
) as p1
pivot
( sum(profit_amount)
for trans_type2 in (ptype1, ptype2)
) as p2
Then we group them on product, like this:
select product, sum(type1) as sales_type1, sum(type2) as sales_type2,
sum(ptype1) as profit_type1, sum(ptype2) as profit_type2
from
( select product, trans_type as trans_type1, ‘p’ + trans_type as trans_type2,
sales_amount, profit_amount
from source_table
) s
pivot
( sum(sales_amount)
for trans_type1 in (type1, type2)
) as p1
pivot
( sum(profit_amount)
for trans_type2 in (ptype1, ptype2)
) as p2
group by product
So can have 2 value columns. We can pivot the table twice.
The key thing is
a) the column for second pivot must be different from the first pivot (e.g. trans_type1 and trans_type2) otherwise SQL Server will complain about duplicate columns, and
b) the value on the 2nd column must be different from the 1st column e.g. ‘type1’ and ‘ptype1’.
If we have a lot of value columns to be pivoted then it’s worth creating a bit of dynamic SQL.
Hope this helps. As usual I welcome any discussion, correction and questions on vrainardi@gmail.com
Data Warehousing and Data Science 