1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

help~ Is it possible to query data like this?

Discussion in 'SQL PL/SQL' started by virusx1984, Jan 27, 2011.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Image that there is a table like this:
    pn date mount
    AAAA 1/7 1000
    AAAA 1/7 1000
    AAAA 1/6 1000
    BBBB 1/4 1000
    CCCC 1/3 1000
    CCCC 1/2 1000
    DDDD 1/4 1000

    How can I use a SQL text to get the query result like this:
    pn 1/2 1/3 1/4 1/6 1/7
    AAAA 0 0 0 1000 2000
    BBBB 0 0 1000 0 0
    CCCC 1000 1000 0 0 0
    DDDD 0 0 1000 0 0

    I can use code or excel's Pivot table to accomplish that problem,but I still want to see if there is a SQL text can do that.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The answer depends upon the release of Oracle you're using; the age-old solution is to use DECODE:

    select pn,
    decode(date, '1/2', mount) "1/2",
    ...

    to generate column headers and column values. In 9i and later releases you can also use CASE:

    select pn,
    case when date = '1/2' then mount end "1/2",
    ...
     
    virusx1984 likes this.
  3. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Thank you!