Home » SQL & PL/SQL » SQL & PL/SQL » fetching values from different columns
fetching values from different columns [message #222743] Tue, 06 March 2007 05:02 Go to next message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear Experts,
I have a problem in ora 9.2.0.1.0 that I can't manage on my own.

I created the following table:

CREATE TABLE RDCOSTTEST1
( ID NUMBER NOT NULL,
STARTYEAR VARCHAR2 (10),
VALUETYPE VARCHAR2 (50),
SCT1VALUE NUMBER,
VAL01 NUMBER,
VAL02 NUMBER,
VAL03 NUMBER,
VAL04 NUMBER,
VAL05 NUMBER,
VAL06 NUMBER,
VAL07 NUMBER,
VAL08 NUMBER,
VAL09 NUMBER,
VAL10 NUMBER,
VAL11 NUMBER,
VAL12 NUMBER,
VAL13 NUMBER,
VAL14 NUMBER,
VAL15 NUMBER,
VAL16 NUMBER,
VAL17 NUMBER,
VAL18 NUMBER,
VAL19 NUMBER
);

with data like:
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (1, '2000', 'TOTALCOST', 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (2, '2000', 'SCL1TOTALCOST', 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (3, '2001', 'TOTALCOST', 0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (4, '2001', 'TOTALCOST', 0, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (5, '2002', 'TOTALCOST', 0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (6, '2002', 'TOTALCOST', 0, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (7, '2003', 'SCL1TOTALCOST', 20, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (8, '2003', 'TOTALCOST', 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (9, '2004', 'SCL1TOTALCOST', 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (10, '2004', 'TOTALCOST', 0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (11, '2005', 'TOTALCOST', 0, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (12, '2005', 'TOTALCOST', 0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (13, '2006', 'TOTALCOST', 0, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (14, '2006', 'SCL1TOTALCOST', 70, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (15, '2007', 'SCL1TOTALCOST', 90, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO RDCOSTTEST1(ID, STARTYEAR, VALUETYPE, SCT1VALUE, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES (16, '2007', 'TOTALCOST', 0, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23);
COMMIT;

for different project costs (valuetype) over 20 years (val00 to val19).

I need to create a query that shows the cost of the actual year and two years back and two years ahead.
so this means cost from 2005 up to 2009.

BUT my problem: if the startyear is e.g. 2000 the cost of year 2005 is val06 and if the startyear is e.g. 2005 the cost of year 2005 is val01 and so on and if the startyear is 2007 there must be not data shown for the year 2006 and 2005. That means
I need a query result like

id valuetype SCL1 2005 2006 2007 2008 2009
-- --------- ---- ---- ---- ---- ---- ----
1 totalcost 0 5 6 7 8 9
2 SCL1totalcost 10 0 0 0 0 0
...
15 SCL1totalcost 90 0 0 0 0 0
16 TOTALCOST 0 0 0 5 6 7

Is it possible to manage a query like this with SQL or do i need to use PL/SQL for this???

Thanks for all kind of support.
kuchp

Re: fetching values from different columns [message #222772 is a reply to message #222743] Tue, 06 March 2007 05:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thank you very much for posting the CREATE and INSERT scripts - its a big help.

One way of doing this is to use a row generator to split each row of data doin into a row per year, and then select from this set of data, and aggregate up the results, like this:

First, split the data down into a row per year
select ID
      ,STARTYEAR
      ,VALUETYPE
      ,SCT1VALUE
      ,startyear + yr value_year
      ,case when yr=1  then VAL01
            when yr=2  then VAL02
            when yr=3  then VAL03
            when yr=4  then VAL04
            when yr=5  then VAL05
            when yr=6  then VAL06
            when yr=7  then VAL07
            when yr=8  then VAL08
            when yr=9  then VAL09
            when yr=10 then VAL10
            when yr=11 then VAL11
            when yr=12 then VAL12
            when yr=13 then VAL13
            when yr=14 then VAL14
            when yr=15 then VAL15
            when yr=16 then VAL16
            when yr=17 then VAL17
            when yr=18 then VAL18
            when yr=19 then VAL19
            END value
from RDCOSTTEST1
    ,(select level yr from dual connect by level < 20)
order by ID
      ,STARTYEAR
      ,VALUETYPE
      ,SCT1VALUE
      ,yr;
This gives you a dataset like:
        ID STARTYEAR  VALUETYPE                                           SCT1VALUE VALUE_YEAR      VALUE
---------- ---------- -------------------------------------------------- ---------- ---------- ----------
         1 2000       TOTALCOST                                                   0       2001          1
         1 2000       TOTALCOST                                                   0       2002          2
         1 2000       TOTALCOST                                                   0       2003          3
         1 2000       TOTALCOST                                                   0       2004          4
         1 2000       TOTALCOST                                                   0       2005          5
         1 2000       TOTALCOST                                                   0       2006          6
         1 2000       TOTALCOST                                                   0       2007          7

Next, you use this query as an inline view, select from it for the year you want, and use MAX and DECODE to get the values for the years you're looking for:
SELECT id
      ,valuetype
      ,sct1value
      ,max(decode(value_year-:year,-2,value,null)) year_minus_2
      ,max(decode(value_year-:year,-1,value,null)) year_minus_1
      ,max(decode(value_year-:year,0,value,null)) year_minus_0
      ,max(decode(value_year-:year,1,value,null)) year_plus_1
      ,max(decode(value_year-:year,2,value,null)) year_plus_2
FROM  (select ID
      ,STARTYEAR
      ,VALUETYPE
      ,SCT1VALUE
      ,startyear + yr value_year
      ,row_number() over (partition by id order by yr) offset
      ,case when yr=1  then VAL01
            when yr=2  then VAL02
            when yr=3  then VAL03
            when yr=4  then VAL04
            when yr=5  then VAL05
            when yr=6  then VAL06
            when yr=7  then VAL07
            when yr=8  then VAL08
            when yr=9  then VAL09
            when yr=10 then VAL10
            when yr=11 then VAL11
            when yr=12 then VAL12
            when yr=13 then VAL13
            when yr=14 then VAL14
            when yr=15 then VAL15
            when yr=16 then VAL16
            when yr=17 then VAL17
            when yr=18 then VAL18
            when yr=19 then VAL19
            END value
from RDCOSTTEST1
    ,(select level yr from dual connect by level < 20))
where :year between value_year-2 and value_year+2
group by id
        ,valuetype
        ,sct1value
order by ID
      ,VALUETYPE
      ,SCT1VALUE;

If you run this query for 2005, you get these results:
        ID VALUETYPE                                           SCT1VALUE YEAR_MINUS_2 YEAR_MINUS_1 YEAR_MINUS_0 YEAR_PLUS_1 YEAR_PLUS_2
---------- -------------------------------------------------- ---------- ------------ ------------ ------------ ----------- -----------
         1 TOTALCOST                                                   0            3            4            5           6           7
         2 SCL1TOTALCOST                                              10            0            0            0           0           0
         3 TOTALCOST                                                   0            3            4            5           6           7
         4 TOTALCOST                                                   0            4            5            6           7           8
         5 TOTALCOST                                                   0            4            5            6           7           8
         6 TOTALCOST                                                   0            5            6            7           8           9
         7 SCL1TOTALCOST                                              20                         0            0           0           0
         8 TOTALCOST                                                   0                         1            2           3           4
         9 SCL1TOTALCOST                                              50                                      0           0           0
        10 TOTALCOST                                                   0                                      2           3           4
        11 TOTALCOST                                                   0                                                  3           4
        12 TOTALCOST                                                   0                                                  4           5
        13 TOTALCOST                                                   0                                                              5
        14 SCL1TOTALCOST                                              70                                                              0
icon7.gif  Re: fetching values from different columns [message #222798 is a reply to message #222772] Tue, 06 March 2007 07:32 Go to previous message
kuchp
Messages: 7
Registered: February 2007
Location: Germany
Junior Member
Dear JRowbottom,

thanks a lot for your answer. I appreciate your help very much and it is working really good and I can do my job quite well with it. I'm really impressed !!!

Thanks again
KUCHP
Previous Topic: there are two column state and city i want single column output like statewise city .
Next Topic: help sql
Goto Forum:
  


Current Time: Sun Dec 04 00:27:32 CST 2016

Total time taken to generate the page: 0.17486 seconds