Home » SQL & PL/SQL » SQL & PL/SQL » unable to retrieve information from...plz help
unable to retrieve information from...plz help [message #230475] Thu, 12 April 2007 01:46 Go to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
hi experts,
i have created a table as follows

table name: truck_transaction
          field             date type
          ---------------   ---------
          delivery_number   number
           description      varchar2(20)
           amount           number



the values that i have inserted are
        delivery_number         description        amount
           1                    bonus                1000
           1                    halting charges      2000
           1                    freight              5000
           2                    bonus                3000
           2                    freight              1500




i want an output in the following format


  delivery_number      bouns         halting charges   freight
    1                  1000           2000              5000
    2                  3000                             1500


plz help me out with a suitable query to retrive data in the above format


thanks in advance

[Mod-edit]added [CODE] and [/CODE] tags

[Updated on: Thu, 12 April 2007 01:58] by Moderator

Report message to a moderator

Re: unable to retrieve information from...plz help [message #230479 is a reply to message #230475] Thu, 12 April 2007 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are "bonus, halting, charges, freight" fixed names or variable ones?
Are there always at most 4 categories or there may be more?
What is your Oracle version?

Regards
Michel
Re: unable to retrieve information from...plz help [message #230514 is a reply to message #230479] Thu, 12 April 2007 02:56 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks..
there are only 3

1) halting charges
2) bonus
3) freight

i am using oracle 9i
Re: unable to retrieve information from...plz help [message #230519 is a reply to message #230514] Thu, 12 April 2007 03:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Then a good old 'decode' and 'max' combination will do:
SQL> -- simulating your table with the "WITH" clause:
SQL> WITH yourtable AS
  2       (SELECT 1 delivery_number
  3             , 'bonus' description
  4             , 1000 amount
  5        FROM   DUAL
  6        UNION ALL
  7        SELECT 1 delivery_number
  8             , 'halting charges' description
  9             , 2000 amount
 10        FROM   DUAL
 11        UNION ALL
 12        SELECT 1 delivery_number
 13             , 'freight' description
 14             , 5000 amount
 15        FROM   DUAL
 16        UNION ALL
 17        SELECT 2 delivery_number
 18             , 'bonus' description
 19             , 3000 amount
 20        FROM   DUAL
 21        UNION ALL
 22        SELECT 2 delivery_number
 23             , 'freight' description
 24             , 1500 amount
 25        FROM   DUAL)
 26  -- your actual select is below:
 27  SELECT   delivery_number
 28         , MAX (DECODE (description, 'bonus', amount, NULL)) bonus
 29         , MAX (DECODE (description, 'halting charges', amount, NULL)) halting
 30         , MAX (DECODE (description, 'freight', amount, NULL)) freight
 31  FROM     yourtable
 32  GROUP BY delivery_number
 33  /

DELIVERY_NUMBER      BONUS    HALTING    FREIGHT
--------------- ---------- ---------- ----------
              1       1000       2000       5000
              2       3000                  1500


MHE

[Updated on: Thu, 12 April 2007 03:05]

Report message to a moderator

Re: unable to retrieve information from...plz help [message #230526 is a reply to message #230519] Thu, 12 April 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey, you stole my answer! Smile

Regards
Michel
Re: unable to retrieve information from...plz help [message #230533 is a reply to message #230526] Thu, 12 April 2007 03:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Thu, 12 April 2007 10:09
Hey, you stole my answer! Smile

Regards
Michel


That's what I do Michel: I pretend I know a lot but I secretly nick other people's ideas. http://www.orafaq.com/forum/fa/1987/0/

MHE
Re: unable to retrieve information from...plz help [message #230555 is a reply to message #230526] Thu, 12 April 2007 03:48 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks a lot
Re: unable to retrieve information from...plz help [message #230582 is a reply to message #230475] Thu, 12 April 2007 04:25 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
its working for the table which i gave..

suppose there are n number of records and if i am getting the delivery number as an runtime parameter?
could u plz help me out


thanks in advance
Re: unable to retrieve information from...plz help [message #230588 is a reply to message #230582] Thu, 12 April 2007 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which records are you talking about?
Overall or distinct descriptions?

Regards
Michel

Re: unable to retrieve information from...plz help [message #230601 is a reply to message #230475] Thu, 12 April 2007 05:00 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
its overall records... is it possible to get the data in the fomat which i have mentioned earlier?... please do let me know the query if it is possible
Re: unable to retrieve information from...plz help [message #230604 is a reply to message #230601] Thu, 12 April 2007 05:14 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten's answer is not dependent of the number of rows.
It depends only on the categories.

Regards
Michel
Previous Topic: how can i get back my emp table 14 rows
Next Topic: extracting information from another table based on the data in the current row
Goto Forum:
  


Current Time: Fri Dec 02 14:03:12 CST 2016

Total time taken to generate the page: 0.05246 seconds