Kindly Help.

[amitgoyal84]

i have 2 columns in a table

ED EF
(numeric) (numeric(7,4))

1 44.23
2 55.39
3 44.25
4 33.78

the outut should be displayed as

col1 col2 col3 col4 col5
1 44.23 2 55.39 difference of col2 and col4
2 55.39 3 44.25 difference of col2 and col4...

and same for other values..

How do i display output in this format.
Kindly help.

[ronverdonk]

Strictly speaking this post belongs in one of the SQL forums. I will, however, reply to it here.

I have tested this using MySQL. This solution will only work under the following strict conditions:

1. the difference between each pair of consecutive ED fields is always 1!
2. you need 2 views to address the intermediate results.

CREATE VIEW v1 AS SELECT * FROM t WHERE ED%2 > 0;
CREATE VIEW v2 AS SELECT * FROM t WHERE ED%2 = 0;
SELECT v1.ED as Col1,v1.EF as Col2, 
    v2.ED as Col3, v2.EF as Col4, 
    v1.EF-v2.EF AS Col2minusCol4 
    FROM v1 JOIN v2 
    WHERE v1.ED+1 = v2.ED;

The result of this on your sample is

+------+---------+------+---------+---------------+
| Col1 | Col2    | Col3 | Col4    | Col2minusCol4 |
+------+---------+------+---------+---------------+
|    1 | 44.2300 |    2 | 55.3900 |      -11.1600 |
|    3 | 44.2500 |    4 | 33.7800 |       10.4700 |
+------+---------+------+---------+---------------+

Ronald