UNPIVOT trick using VALUES clause

Pinal Dave, Blogger and Speaker, posted about SQL SERVER – Creating Dataset Using VALUES Clause Without Creating A Table  Using VALUES clause you can also do what UNPIVOT does in SQL Server

Consider the following data set

create table #t (id int , amount1 decimal(12,2), amount2 decimal(12,2), amount3 decimal(12,2)) insert into #t select 1, 200, 267.40, 188.90
insert into #t select 2, 455.75, 310.25, 4000
insert into #t select 3, 200, 1900, 450
insert into #t select 4, 230.80, 36000, 2100
insert into #t select 5, 1200, 900, 2500.10

If you want to find out the maximum value available in the columns amount1, amount2 and amount3 for each id, you can use UNPIVOT operator

select id,max(amount) as max_amount from #t
unpivot (amount for column_name in (amount1,amount2,amount3)) as t
group by id

The result is

Id      max_amount
----  --------------------
1         267.40
2        4000.00
3        1900.00
4       36000.00
5        2500.00

The same can be done using VALUES clause from version 2008 onwards

select id,
(
select max(amount) from (values(amount1),(amount2),(amount3)) as t (amount)
) as max_amount
from #t

The result is

Id      max_amount
----  --------------------
1         267.40
2        4000.00
3        1900.00
4       36000.00
5        2500.00

Advertisements

5 thoughts on “UNPIVOT trick using VALUES clause

  1. cool stuff Madhi!!!

    Just Another variation.

    select id,MAX(CrossVals.amount)
    from #t
    cross apply(values(amount1),(amount2),(amount3)) CrossVals(amount)
    Group by id

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