Pivot with Math Ops? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot with Math Ops?

Author

Topic

slstrozier
Starting Member

1 Post

Posted - 2012-07-20 : 16:28:14

I have a table like so....

productId _year amount
124 2001 125
125 2001 454
126 2002 75
126 2001 256

I use the following function to pivot the columns....

SELECT * FROM
(
SELECT productId,_year,amount
FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ([2001],[2002])) AS pvt

And I get the resulting table....

productId 2001 2002
124 125 NULL
125 454 NULL
126 256 75

How can I do a math function on the resulting table? Id like to get the difference of the values of the columns into a new Column.

Like so...

productId 2001 2002 Totals
124 125 NULL 125
125 454 NULL 454
126 256 75 181

Any help would be greatly appreciated for I have spent an entire day on this.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-20 : 17:10:16

untested but shouldn't this do it?

SELECT *, [200]-[2002] as totals FROM
(
SELECT productId,_year,amount
FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ([2001],[2002])) AS pvt

EDIT:
of course you would need to isnull or coalesce the NULLs:
[2001] - coalesce([2002],0)

Be One with the Optimizer
TG

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-20 : 17:13:44

i thought of that too but what if there is a span of years.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 22:38:17

quote:
Originally posted by slstrozier

I have a table like so....

productId _year amount
124 2001 125
125 2001 454
126 2002 75
126 2001 256

I use the following function to pivot the columns....

SELECT * FROM
(
SELECT productId,_year,amount
FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ([2001],[2002])) AS pvt

And I get the resulting table....

productId 2001 2002
124 125 NULL
125 454 NULL
126 256 75

How can I do a math function on the resulting table? Id like to get the difference of the values of the columns into a new Column.

Like so...

productId 2001 2002 Totals
124 125 NULL 125
125 454 NULL 454
126 256 75 181

Any help would be greatly appreciated for I have spent an entire day on this.

see

http://visakhm.blogspot.com/2012/04/display-total-rows-with-pivotting-in-t.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources