johnpienaar
Technical User
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
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