Basic Addition/Subtration/Division etc between rows

[johnpienaar]

Hi
I am trying to figure out a way to perform basic operations between rows, which would be best demonstrated using an example:

Key Name P01BY P02BY P03BY
1 Volumes (Current Year) 10 20 30
2 Net Revenue (CurrentY) 10 20 30
3 % New Business Volume 0.5 0.2 0.5
4 New Business Volume 8 8 8
5 % Market Growth Volume 0.5 0.2 0.5

Here for instance I would need to say that for all three data columns the "New Business Volume" rows values need to be equal to "% New Business Volume" * "Volumes (Current Year)"
There are actually 12 columns of data, and the closest I can get is to write an SQL statement for each row that has nested select statements.
I know the most efficient solution would be to swop the columns and rows, but the rest of the database is stored in this format, and I guess I would probably have to pivot the data if I did this, and I don't wanna.
Anyone got some handy suggestions for keeping my SQL code neat and efficient.
There's a Mars bar going for most efficient solution (but not really...)
Thanks
John

 

[philhege]

The data's denormalized, which means that any attempt at "normal" SQL is doomed from the start. You probably have to bite the bullet and de-pivot it, or look into Analysis Services and warehouse this stuff.

Good luck.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[vongrunt]

> Here for instance I would need to say that for all three data columns the "New Business Volume" rows values need to be equal to "% New Business Volume" * "Volumes (Current Year)"

update T1
set P01BY = T2.P01BY * T3.P01BY, 
	 P02BY = T2.P02BY * T3.P02BY, 
	 P03BY = T2.P03BY * T3.P03BY
from myTable T1, myTable T2, myTable T3
where T1.Name = 'New Business Volume'
	and T2.Name = '% New Business Volume'
	and T3.Name = 'Volumes (Current Year)'

> I know the most efficient solution would be to swop the columns and rows, but the rest of the database is stored in this format, and I guess I would probably have to pivot the data if I did this, and I don't wanna.

Ouch... OK repeatable columns (I can on that with great mental effort :X), but single column used for different purposes usually means mutual dependency between rows - and then whole SQL thing goes .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[johnpienaar]

Thanks for the input- was hoping there was some way to do this easily, but I guess I'll try and normalise and write an update to the other tables.
Thanks.