6 Replies Latest reply: Jan 3, 2013 8:13 AM by pallis RSS

    Looking for Query..

    pallis
      sno name price date
      1 a 200 jan 2012
      2 b 300 feb 2012
      3 c 400 sep 2012
      4 d 260 dec 2012

      Output need:

      sno jan feb mar apr may jun jul aug sep oct nov dec
      1 200
      2 300
      3 400
      4 260


      I tried query like this. But for every month i have to write decode. Instead of this is there any alternative way. I need to design in Oracle Reports 6i.

      SELECT sno,
      MAX(DECODE(date,jan, price))Jan,
      MAX(DECODE(date,feb, price))feb,
      MAX(DECODE(date,apr, price))mar,
      -----
      -----
      MAX(DECODE(date,dec, price))dec
      FROM PRODUCTS

      Is ther any other way to write this sql
      DB:9i

      Thanks & Regards
      pallis
        • 1. Re: Looking for Query..
          ranit B
          --Go for PIVOTing--

          Sorry, my mistake... i didn't notice that.

          Then i guess either DECODE or using CASE with MAX (or SUM) should work.
          But the basic idea remains the same, what you already have.

          Edited by: ranit B on Jan 3, 2013 5:31 PM
          • 2. Re: Looking for Query..
            pallis
            Im using oracle 9im d2k reports 6i.
            • 3. Re: Looking for Query..
              _Karthick_
              I tried query like this. But for every month i have to write decode. Instead of this is there any alternative way. I need to design in Oracle Reports 6i.
              Thas correct the only way is to write decode for every month. There are only 12 months must not be a big trouble :)
              • 4. Re: Looking for Query..
                pallis
                Can u help me how to achive in d2K reports 6i
                • 5. Re: Looking for Query..
                  Frank Kulash
                  Hi, Pallis,
                  pallis wrote:
                  sno name price date
                  1 a 200 jan 2012
                  2 b 300 feb 2012
                  3 c 400 sep 2012
                  4 d 260 dec 2012
                  Always post CREATE TABLE and INSERT statements for your sample data.
                  See the forum FAQ {message:id=9360002}
                  Output need:

                  sno jan feb mar apr may jun jul aug sep oct nov dec
                  1 200
                  2 300
                  3 400
                  4 260
                  That looks like the feb-dec columns are always NULL. Is that what you meant, or did you mean something like this:
                  sno   jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec
                    1   200                                  
                    2         300
                    3                                             400
                    4                                                                     260
                  ? The same forum FAQ page explains how to use \
                   tags to post formatted text on this site. 
                  
                  I tried query like this. But for every month i have to write decode. Instead of this is there any alternative way. I need to design in Oracle Reports 6i.

                  SELECT sno,
                  MAX(DECODE(date,jan, price))Jan,
                  MAX(DECODE(date,feb, price))feb,
                  MAX(DECODE(date,apr, price))mar,
                  -----
                  -----
                  MAX(DECODE(date,dec, price))dec
                  FROM PRODUCTS
                  DATE is not a good column name. How are you defining variables such as jan that are used in the DECODE expressions?  You can use the SYSDATE and ADD_MONTHS functions to dynamically set them relative to today's date.  For the column aliases, you may need dynamic SQL.  (In SQL*Plus, this is fairly easy, using substitution variables.)  When you post the sample data, give a couple of different examples of output you would want from the exact same data.  E.g. "If I run the query today, or any time in January 2013, then I should get this output: ...  but if I run it in February 2013, then I want ..."
                  Is ther any other way to write this sql
                  Yes, therres another forum FAQ page on this topic. {message:id=9360005} Do you need 12 separate columns for the months, or would you accept one big string column, formatted to look like 12 columns?  String aggregation (which does *not* require dynamic SQL) might be an option.
                  DB:9i
                  Are you saying you're database version is Oracle 9?  What is the actual version number, e.g. 9.2.0.6.0? I don't know if Oracle Reports can pivot data.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                  • 6. Re: Looking for Query..
                    pallis
                    @ Frank I typed like that but i don't know how it changed.