Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: is there a better way?

    Setup: 9iDB

    I have a table like thus

    ID Name Value
    ----- ------- --------
    1 item1 value
    2 vol1 value
    3 dens1 value
    4 item2 value
    5 vol2 value
    6 dens2 value
    7 item3 value
    8 vol3 value
    9 dens3 value

    I want to display the info like

    value(of item1) value(of item2) value(of item3)
    value(of vol1) value(of vol2) value(of vol3)
    value(of dens1) value(of dens2) value(of dens3)

    The best I have come up with is separate queries for each line as in

    select distinct (select value
    from table
    where id = 1) "item1",
    (select value
    from table
    where id = 4) "item2",
    (select value
    from table
    where id = 7) "item3"
    from table o
    WHERE Parameter_id in (1, 4, 7);

    select distinct (select value
    from table
    where id = 2) "vol1",
    (select value
    from table
    where id = 5) "vol2",
    (select value
    from table
    where id = 8) "vol3"
    from table o
    WHERE Parameter_id in (2, 5, 8);

    select distinct (select value
    from table
    where id = 3) "dens1",
    (select value
    from table
    where id = 6) "dens2",
    (select value
    from table
    where id = 9) "dens3"
    from table o
    WHERE Parameter_id in (3, 6, 9);


    I am thinking there must be an easier or better way to accomplish. I tried a decode for a bit but that didn't seem to be going anywhere.

    Any ideas? Thanks
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: is there a better way?

    Try this:
    PHP Code:
    select substr(name,1,length(name)-1)
    ,      
    max(DECODE(substr(name,-1),'1'value)) as "1"
    ,      max(DECODE(substr(name,-1),'2'value)) as "2"
    ,      max(DECODE(substr(name,-1),'3'value)) as "3"
    from   t
    group by substr
    (name,1,length(name)-1)
    order by decode(substr(name,1,length(name)-1),'item',1,'vol',2,3); 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •