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!

Value in columns (pivot query)

Discussion in 'SQL PL/SQL' started by kumarvk, Feb 21, 2012.

  1. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi Team,

    I have the following sql
    SELECT distinct b.svmd_code,a.job_code,a.eng_dsc, count(A.JOB_CODE) "HIT_RT"
    FROM MSS_RO_JOBS A,
    mss_ro_hdr b
    WHERE A.STS ='I'
    and A.ROHD_ID = B.ID
    and B.CUR_MILEAGE between '0' and '10000'
    -- and B.CUR_MILEAGE between '10000' and '20000'
    -- and B.CUR_MILEAGE between '20001' and '30000'
    -- and B.CUR_MILEAGE between '30001' and '40000'
    -- and B.CUR_MILEAGE between '40001' and '50000'

    I need to run this sql for each cur_mileage rather I want to use pivot query for the above and have the result like this

    SELECT distinct b.svmd_code,a.job_code,a.eng_dsc,
    DECODE( CONDITON,count(A.JOB_CODE) AS "10000",
    DECODE( CONDITON,count(A.JOB_CODE) AS "20000",
    DECODE( CONDITON,count(A.JOB_CODE) AS "30000",
    DECODE( CONDITON,count(A.JOB_CODE) AS "40000",
    DECODE( CONDITON,count(A.JOB_CODE) AS "50000"
    FROM MSS_RO_JOBS A,
    mss_ro_hdr b
    WHERE A.STS ='I'
    and A.ROHD_ID = B.ID
    GROUP BY b.svmd_code,a.job_code,a.eng_dsc,

    Can anyone help me in this decode by using pivor_query or concatenate?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Use CASE Instead of decode as below.

    Code (SQL):

    SQL> SELECT  DEPTNO , COUNT(CASE WHEN SAL BETWEEN 1 AND 1000 THEN 1 END) "SLAB_1000",
      2                                 COUNT(CASE WHEN SAL BETWEEN 1001 AND 2000 THEN 1 END) "SLAB_2000",
      3                                 COUNT(CASE WHEN SAL BETWEEN 2001 AND 3000 THEN 1 END) "SLAB_3000"
      4  FROM EMP
      5  GROUP BY DEPTNO;

        DEPTNO  SLAB_1000  SLAB_2000  SLAB_3000
    ---------- ---------- ---------- ----------
            30          1          4          1
            20          1          1          2
            10          0          0          1

    SQL>
     
     
  3. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi Raj,

    Sorry to say that I am still using Oracle 8i. I believe that CASE works only in 9i. Any other option in 8i instead of CASE.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No.. Case was introduced in Oracle 8i. You just try and let us know the status.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Anyway, Here is another workaround for the same issue with Decode. But it is not as simple as the solution with CASE.

    Code (SQL):

    SQL> SELECT  DEPTNO , COUNT(DECODE(SIGN(SAL-1000),-1,1))"SLAB_1000",
      2                   COUNT(DECODE(SIGN(SAL-2000),-1,
      3                                DECODE(SIGN(SAL-1000),-1,NULL,1)))"SLAB_2000",
      4                   COUNT(DECODE(SIGN(SAL-3000),-1,
      5                                DECODE(SIGN(SAL-2000),-1,NULL,1)))"SLAB_3000"
      6  FROM EMP
      7  GROUP BY DEPTNO;

        DEPTNO  SLAB_1000  SLAB_2000  SLAB_3000
    ---------- ---------- ---------- ----------
            30          1          4          1
            20          1          1          1
            10          0          0          1

    SQL>
     
     
  6. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    hi Raj,

    Actually my current sql is like this

    SELECT distinct b.svmd_code,a.job_code,a.eng_dsc,
    count(A.JOB_CODE) "10000"
    FROM MSS_RO_JOBS A,
    mss_ro_hdr b
    WHERE A.STS ='I'
    and A.ROHD_ID = B.ID
    and b.mileage between 0 and 10000

    My result like this
    SVMD_CODE CODE ENG_DSC 10000
    N LIGHT DUTY TRUCK T0145 DIAGNOSIS / SYSTEM RESET WITH TECH II 643
    N LIGHT DUTY TRUCK L1001 DOWNLOAD DRM 559
    N LIGHT DUTY TRUCK J6541 DPD SERVICE REGENERATION 451
    N LIGHT DUTY TRUCK V0003 CHECK AIR COND NOT COLD 300
    N LIGHT DUTY TRUCK V0012 CHECK DPD FLASHING & MIL LIGHT ON 263
    N LIGHT DUTY TRUCK V0005 CHECK ENGINE OIL DILUTION 234
    N LIGHT DUTY TRUCK V0006 OIL DILUTION AT 1/4 MARK AFTER MAX LEVEL 146

    Now I want to use the CASE and have columns for 10000,20000,30000,40000 etc.
    so each job code will be counted depending upon the mileage rather than using the mileage in where statement for each mileage.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    By the way, Why can't you use Tags in you post to make the codes more readable?
     
  8. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    As you requested
     
  9. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):

    SELECT DISTINCT B.SVMD_CODE,A.JOB_CODE,A.ENG_DSC,
           COUNT(A.JOB_CODE) "10000"
    FROM MSS_RO_JOBS A,
         mss_ro_hdr b
    WHERE A.STS ='I'
    AND A.ROHD_ID = B.ID
    AND b.mileage BETWEEN 0 AND 10000
     
     
  10. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    The result
    Code (SQL):

    SVMD_CODE          CODE  ENG_DSC                                  10000
    N LIGHT DUTY TRUCK T0145 DIAGNOSIS / SYSTEM RESET WITH TECH II    643
    N LIGHT DUTY TRUCK L1001 DOWNLOAD DRM                             559
    N LIGHT DUTY TRUCK J6541 DPD SERVICE REGENERATION                 451
    N LIGHT DUTY TRUCK V0003 CHECK AIR COND NOT COLD                  300
    N LIGHT DUTY TRUCK V0012 CHECK DPD FLASHING & MIL LIGHT ON        263
    N LIGHT DUTY TRUCK V0005 CHECK ENGINE OIL DILUTION                234
    N LIGHT DUTY TRUCK V0006 OIL DILUTION AT 1/4 MARK AFTER MAX LEVEL 146
     
     
  11. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Code (Text):

    SELECT DISTINCT b.svmd_code,a.job_code,a.eng_dsc,
    COUNT(A.JOB_CODE) "10000"
    FROM MSS_RO_JOBS A,
    mss_ro_hdr b
    WHERE A.STS ='I'
    AND A.ROHD_ID = B.ID
    AND b.mileage BETWEEN 0 AND 10000.
     
     
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Use highlight code to highlight the code.
    Anyway, The following code may work for you.

    Code (SQL):

    SELECT B.SVMD_CODE,A.JOB_CODE,A.ENG_DSC,
           COUNT(CASE WHEN B.MILEAGE BETWEEN 0 AND 10000 THEN 1 END) "SLAB_10000",
           COUNT(CASE WHEN B.MILEAGE BETWEEN 10001 AND 20000 THEN 1 END) "SLAB_20000",
           COUNT(CASE WHEN B.MILEAGE BETWEEN 20001 AND 30000 THEN 1 END) "SLAB_30000",
           COUNT(CASE WHEN B.MILEAGE BETWEEN 30001 AND 40000 THEN 1 END) "SLAB_40000"
    FROM MSS_RO_JOBS A,
         MSS_RO_HDR B
    WHERE A.STS ='I'
    AND A.ROHD_ID = B.ID
    GROUP BY B.SVMD_CODE,A.JOB_CODE,A.ENG_DSC
     
     
  13. kumarvk

    kumarvk Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi Raj,

    Actually I want to count the job_codes for the mileage being used. I dont want to count the mileage for the job_codes.

    for example the job_code is L1001 I want to count on this job_code whenever the mileage is between 0-10000, 10001-20000, 20001-30000 etc

    Job Code MIL1 MIL2 MIL3 MIL4
    L1001 10 65 30 10

    Did you got my point Raj.
     
  14. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    My query is just counting the records of job codes where mileage is between x and y. It is not counting the mileage.

    Otherwise.. provide us the test table create and insert scripts along with the expected output.