| Author |
 Topic  |
|
|
slstrozier
Starting Member
1 Posts |
 Posted - 07/20/2012 : 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
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 07/20/2012 : 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 |
Edited by - TG on 07/20/2012 17:11:52 |
 |
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 07/20/2012 : 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
India
47189 Posts |
Posted - 07/20/2012 : 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/
|
|
|
| |
Topic |
|
Pivot with Math Ops?
