Results 1 to 2 of 2

Thread: help on query

  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: help on query

    Hi,
    I've this table:
    CREATE TABLE CS
    (
    MACROS VARCHAR2(128),
    SOC VARCHAR2(128),
    MED NUMBER,
    PERC NUMBER
    );

    MACROS.....SOC.........MED...........PERC
    CRM........MAT............3............60
    CRM........ELE...........3,67..........46,67
    CRM........LOQ...........2,56..........68,89
    CRM........TIA...........4,5..........30
    CRM........TIM...........3,05..........59,02
    CRM........TIS..........3,76..........44,79
    CRM........TEL..........2,65..........67,02
    CRM........TEC..........3,3..........53,92

    AMB........MAT..........3,3............54,05
    AMB........ELE.........2,22..........75,56
    AMB........LOQ.........2,96..........60,79
    AMB........TIA.........4,37..........32,59
    AMB........TIM.........2,65..........67,02
    AMB........TIS.........3,82..........43,63
    AMB........TEL.........2,58..........68,47
    AMB........TEC.........3,31..........53,82

    MOT........MAT...............................
    MOT........ELE.........2,58..........68,48
    MOT........LOQ............................
    MOT........TIA.........4,5.............30
    MOT........TIM............................
    MOT........TIS.........3,07..........58,68
    MOT........TEL.........3,21..........55,77
    MOT........TEC.........3,14..........57,16

    I'd like to get this output:

    SOC....CRM_MED....CRM_PERC.....AMB_MED.....AMB_PER C.....MOT_MED.....MOT_PERC
    MAT......3..............60................3,3..... .........54,05.................................... .......
    ELE......3,67..........46,67..........2,22........ .....75,56..............2,58...............68,48
    LOQ......2,56..........68,89...........2,96....... ......60,79....................................... ...
    TIA......4,5..........30................4,37...... ........32,59................4,5.................3 0
    TIM......3,05..........59,02..........2,65........ .......67,02...................................... ...
    TIS......3,76..........44,79........3,82.......... .....43,63................3,07................58,6 8
    TEL......2,65..........67,02........2,58.......... .....68,47...............3,21................55,77
    TEC......3,3..........53,92.........3,31.......... ......53,82................3,14.................57 ,16

    How Can I write my query to get this output?

    Thanks in advance!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Code:
    select
    soc,
    max(case when macros='CRM' then med end) CRM_MED,
    max(case when macros='CRM' then perc end) CRM_PERC,
    ...
    from cs
    group by soc
    Alan

Posting Permissions

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