Data Warehousing and Business Intelligence

11 August 2010

Using Pivot for 2 Value Columns

Filed under: SQL Server — Vincent Rainardi @ 3:08 pm
Tags:

Say you need to pivot this table:

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)
f
or 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

4 Comments »

  1. Hi, The suggested solution is working but not in all cases. Unfortunately when you’re pivotting twice, some of the values won’t come in. I’ve made a proc in a similar way but the results are not as expected. The better solution which is working and gives right data could be found on http://www.sqlusa.com/articles2005/crosstab2measures/.
    Best regard,
    Ireneus Pisanko
    BI consultant

    Comment by Ireneusz Pisanko-Krzyzanowski — 30 April 2011 @ 6:31 pm | Reply

  2. Thanks for the the post

    Comment by Sunniawaty Prayugo — 19 June 2015 @ 3:33 am | Reply

  3. how can i add prefix when I am passing columns list using a variable(dynamic)

    Comment by Akhil — 26 November 2015 @ 9:29 am | Reply

  4. thanks! it works!

    Comment by os ch — 15 August 2016 @ 6:58 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: