column to row (computed)

From: Ritesh G. <garodiar_at_yahoo.com>
Date: 25 Oct 2002 16:10:36 -0700
Message-ID: <1ada02e0.0210251510.5f0c01b5@posting.google.com>

hi,

I wanted to know if one can do following with one/two/more SQL select statements, (may be using some oracle functions also)

Suppose there is a table tab1 as

Key State Value

---

P1      STATE1   Value1
P1      STATE2   Value2
P2      STATE1   Value3
P2      STATE2   Value4

Now the result desired is following with two columns

P1   (Value1 - Value2)        ie { STATE1 - STATE2}
P2   (Value3 - Value4)        ie { STATE1 - STATE2}

In short for each key in table we get (state, value) pairs, then get a computed state based on state values and rule as 'STATE1's Value - 'STATE2's Value.

Something like

select key, sum (case when State = "STATE1" then + Value

                      when State = "STATE2" then - Value)

from tab1
group by key

Thanks
Ritesh
garodiar_at_yahoo.com Received on Fri Oct 25 2002 - 18:10:36 CDT