This discussion is archived
12 Replies Latest reply: Jan 7, 2013 3:07 AM by 981984 RSS

How to use Pivot function for group range in oracle SQL

981984 Newbie
Currently Being Moderated
Hi,

Good Morning !!!

I need to show the data in the below format. There is 2 columns 1 is State and another one is rate.

State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total
AK     1     2     0     4     1     4     4     35     35     4     1     25
AL     0     0     2     27     10     17     35     2     2     35     0     103
AR     0     0     1     0     0     2     2     13     13     2     0     6
AZ     0     1     2     14     2     14     13     3     3     13     0     57
CA     0     0     1     6     2     7     3     4     4     3     0     34

Developed the below query but unable to use the range on pivot function . Please help on this.

(select      (SELECT SHORT_DESCRIPTION
     FROM CODE_VALUES
     WHERE CODE_TYPE_CODE = ad.STATE_TYPE_IND_CODE
     AND VALUE = ad.STATE_CODE
     ) STATE,
nr.rate
     FROM neutrals n,
     contacts c,
     addresses ad,
     xref_contacts_addresses xca,
     neutral_rates nr
                    where n.contact_id=c.contact_id
                    and n.address_id = ad.address_id
                    and xca.address_id=ad.address_id
                    and xca.contact_id=c.contact_id
                    and nr.contact_id = n.contact_id
                    and nr.rate_frequency='HOUR' )
  • 1. Re: How to use Pivot function for group range in oracle SQL
    Karthick_Arp Guru
    Currently Being Moderated
    user8564931 wrote:
    Hi,

    Good Morning !!!

    I need to show the data in the below format. There is 2 columns 1 is State and another one is rate.
    *State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total*
    *AK     1     2     0     4     1     4     4     35     35     4     1     25*
    *AL     0     0     2     27     10     17     35     2     2     35     0     103*
    *AR     0     0     1     0     0     2     2     13     13     2     0     6*
    *AZ     0     1     2     14     2     14     13     3     3     13     0     57*
    *CA     0     0     1     6     2     7     3     4     4     3     0     34*
    Developed the below query but unable to use the range on pivot function . Please help on this.
    (select      (SELECT SHORT_DESCRIPTION 
          FROM CODE_VALUES 
          WHERE CODE_TYPE_CODE = ad.STATE_TYPE_IND_CODE 
           AND VALUE = ad.STATE_CODE
         ) STATE,
    nr.rate
         FROM neutrals n,
                  contacts c,
                 addresses ad,
                 xref_contacts_addresses xca,
                 neutral_rates nr
                        where n.contact_id=c.contact_id
                        and n.address_id = ad.address_id
                        and xca.address_id=ad.address_id
                        and xca.contact_id=c.contact_id        
                        and nr.contact_id = n.contact_id
                        and nr.rate_frequency='HOUR'  )
    Welcome to the forum!!

    I would suggest you to read {message:id=9360002} This helps in getting your question answered quickly.

    Some informatino like Oracle version (4 digit version) and Sample input data (In the form of Create table and Insert statement or a WITH clause) would be really helpful.
  • 2. Re: How to use Pivot function for group range in oracle SQL
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Are you asking about the Oracle 11 SELECT ... PIVOT feature? That doesn't accept ranges, only discreet values, but you can convert the ranges into discreet values using a CASE expression. For example:
    CASE
        WHEN  rate <  100  THEN  0
        WHEN  rate > 1000  THEN  10
                           ELSE  TRUNC (rate / 100)
    END
    Do this in a sub-query, before doing the PIVOT.

     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Simplify the problem as much as possible. For example, if your query requires 6 tables, but the part you need help with only involves 1 or 2 tables, then post a problem with only those 1 or 2 tables.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 3. Re: How to use Pivot function for group range in oracle SQL
    981984 Newbie
    Currently Being Moderated
    Thanks for your reply.

    I tried your solution but its not fullfilling my requirement, because I have to show multiple columns for the rate range like <100 || 100-199 || 200-299 || 300-399 .

    My requirement is There is State and rate columns.

    In the left side need to show State column with in that state <100 || 100-199 || 200-299 || 300-399 if any rate is there then need the count.

    Existing query is giving the data in the below format_

    AL     12
    AL     67
    AL     23
    AL     12
    AL     12
    AL     78
    AL     34
    AL     4
    AL     12
    AL     15
    AZ     6
    AZ     123
    AZ     123
    MA     23
    MA     120
    MA     456
    MA     11
    MA     24
    MA     34
    MA     87
    MA     23
    MA     234
    MA     789
    MH     54321

    My Requirement is to show this data is


    State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total
    AK     0     0     0     0     0     0     0     0     3     4     1     8
    AL     10     0     0     0     0     0     0     2     2     35     1     50
    AR     0     0     1     0     0     2     2     13     13     2     0     6
  • 4. Re: How to use Pivot function for group range in oracle SQL
    Karthick_Arp Guru
    Currently Being Moderated
    Like this
    with t
    as
    (
    select 'AL' state, 12 value from dual union all
    select 'AL' state, 67 value from dual union all
    select 'AL' state, 23 value from dual union all
    select 'AL' state, 12 value from dual union all
    select 'AL' state, 12 value from dual union all
    select 'AL' state, 78 value from dual union all
    select 'AL' state, 34 value from dual union all
    select 'AL' state, 4 value from dual union all
    select 'AL' state, 12 value from dual union all
    select 'AL' state, 15 value from dual union all
    select 'AZ' state, 6 value from dual union all
    select 'AZ' state, 123 value from dual union all
    select 'AZ' state, 123 value from dual union all
    select 'MA' state, 23 value from dual union all
    select 'MA' state, 120 value from dual union all
    select 'MA' state, 456 value from dual union all
    select 'MA' state, 11 value from dual union all
    select 'MA' state, 24 value from dual union all
    select 'MA' state, 34 value from dual union all
    select 'MA' state, 87 value from dual union all
    select 'MA' state, 23 value from dual union all
    select 'MA' state, 234 value from dual union all
    select 'MA' state, 789 value from dual union all
    select 'MH' state, 54321 value from dual 
    )
    -- End of test data
    select state, 
           nvl(count(decode(value, 0, 0)), 0) "<100",
           nvl(count(decode(value, 1, 1)), 0) "100-199",
           nvl(count(decode(value, 2, 2)), 0) "200-299",
           nvl(count(decode(value, 3, 3)), 0) "300-399",
           nvl(count(decode(value, 4, 4)), 0) "400-499",
           nvl(count(decode(value, 5, 5)), 0) "500-599",
           nvl(count(decode(value, 6, 6)), 0) "600-699",
           nvl(count(decode(value, 7, 7)), 0) "700-799",
           nvl(count(decode(value, 8, 8)), 0) "800-899",
           nvl(count(decode(value, 9, 9)), 0) "900-999",
           nvl(count(decode(value, 10, 10)), 0) ">=1000"
      from (
              select state, case when value < 100 then 0
                                 when value between 100 and 199 then 1
                                 when value between 200 and 299 then 2
                                 when value between 300 and 399 then 3
                                 when value between 400 and 499 then 4
                                 when value between 500 and 599 then 5
                                 when value between 600 and 699 then 6
                                 when value between 700 and 799 then 7
                                 when value between 800 and 899 then 8
                                 when value between 900 and 999 then 9
                                 when value >= 1000 then 10
                            end value
                from t
           )
     group
        by state
  • 5. Re: How to use Pivot function for group range in oracle SQL
    981984 Newbie
    Currently Being Moderated
    Its working for my requirement.

    Thank you So Much for your kind Reply.

    Can you please tell me how can i get the total of that rows value like below


    State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total
    AK     1     2     0     4     1     4     4     35     35     4     1     25
    AL     0     0     2     27     10     17     35     2     2     35     0     103
    AR     0     0     1     0     0     2     2     13     13     2     0     6
  • 6. Re: How to use Pivot function for group range in oracle SQL
    Karthick_Arp Guru
    Currently Being Moderated
    user8564931 wrote:
    Its working for my requirement.

    Thank you So Much for your kind Reply.

    Can you please tell me how can i get the total of that rows value like below


    State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total
    AK     1     2     0     4     1     4     4     35     35     4     1     25
    AL     0     0     2     27     10     17     35     2     2     35     0     103
    AR     0     0     1     0     0     2     2     13     13     2     0     6
    You can just add the column values
    select state, 
           nvl(count(decode(value, 0, 0)), 0) "<100",
           nvl(count(decode(value, 1, 1)), 0) "100-199",
           nvl(count(decode(value, 2, 2)), 0) "200-299",
           nvl(count(decode(value, 3, 3)), 0) "300-399",
           nvl(count(decode(value, 4, 4)), 0) "400-499",
           nvl(count(decode(value, 5, 5)), 0) "500-599",
           nvl(count(decode(value, 6, 6)), 0) "600-699",
           nvl(count(decode(value, 7, 7)), 0) "700-799",
           nvl(count(decode(value, 8, 8)), 0) "800-899",
           nvl(count(decode(value, 9, 9)), 0) "900-999",
           nvl(count(decode(value, 10, 10)), 0) ">=1000",
           nvl(count(decode(value, 0, 0)), 0) +
           nvl(count(decode(value, 1, 1)), 0) +
           nvl(count(decode(value, 2, 2)), 0) +
           nvl(count(decode(value, 3, 3)), 0) +
           nvl(count(decode(value, 4, 4)), 0) +
           nvl(count(decode(value, 5, 5)), 0) +
           nvl(count(decode(value, 6, 6)), 0) +
           nvl(count(decode(value, 7, 7)), 0) +
           nvl(count(decode(value, 8, 8)), 0) +
           nvl(count(decode(value, 9, 9)), 0) +
           nvl(count(decode(value, 10, 10)), 0) total
      from (
              select state, case when value < 100 then 0
                                 when value between 100 and 199 then 1
                                 when value between 200 and 299 then 2
                                 when value between 300 and 399 then 3
                                 when value between 400 and 499 then 4
                                 when value between 500 and 599 then 5
                                 when value between 600 and 699 then 6
                                 when value between 700 and 799 then 7
                                 when value between 800 and 899 then 8
                                 when value between 900 and 999 then 9
                                 when value >= 1000 then 10
                            end value
                from t
           )
     group
        by state
  • 7. Re: How to use Pivot function for group range in oracle SQL
    Manik Expert
    Currently Being Moderated
    Just extending Karthik's response for getting TOTAL.
    WITH t AS
            (SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 67 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 23 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 78 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 34 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 4 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 15 VALUE FROM DUAL
             UNION ALL
             SELECT 'AZ' state, 6 VALUE FROM DUAL
             UNION ALL
             SELECT 'AZ' state, 123 VALUE FROM DUAL
             UNION ALL
             SELECT 'AZ' state, 123 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 23 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 120 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 456 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 11 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 24 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 34 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 87 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 23 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 234 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 789 VALUE FROM DUAL
             UNION ALL
             SELECT 'MH' state, 54321 VALUE FROM DUAL),
         -- End of test data
         t1 AS
            (  SELECT state,
                      NVL (COUNT (DECODE (VALUE, 0, 0)), 0) "<100",
                      NVL (COUNT (DECODE (VALUE, 1, 1)), 0) "100-199",
                      NVL (COUNT (DECODE (VALUE, 2, 2)), 0) "200-299",
                      NVL (COUNT (DECODE (VALUE, 3, 3)), 0) "300-399",
                      NVL (COUNT (DECODE (VALUE, 4, 4)), 0) "400-499",
                      NVL (COUNT (DECODE (VALUE, 5, 5)), 0) "500-599",
                      NVL (COUNT (DECODE (VALUE, 6, 6)), 0) "600-699",
                      NVL (COUNT (DECODE (VALUE, 7, 7)), 0) "700-799",
                      NVL (COUNT (DECODE (VALUE, 8, 8)), 0) "800-899",
                      NVL (COUNT (DECODE (VALUE, 9, 9)), 0) "900-999",
                      NVL (COUNT (DECODE (VALUE, 10, 10)), 0) ">=1000"
                 FROM (SELECT state,
                              CASE
                                 WHEN VALUE < 100 THEN 0
                                 WHEN VALUE BETWEEN 100 AND 199 THEN 1
                                 WHEN VALUE BETWEEN 200 AND 299 THEN 2
                                 WHEN VALUE BETWEEN 300 AND 399 THEN 3
                                 WHEN VALUE BETWEEN 400 AND 499 THEN 4
                                 WHEN VALUE BETWEEN 500 AND 599 THEN 5
                                 WHEN VALUE BETWEEN 600 AND 699 THEN 6
                                 WHEN VALUE BETWEEN 700 AND 799 THEN 7
                                 WHEN VALUE BETWEEN 800 AND 899 THEN 8
                                 WHEN VALUE BETWEEN 900 AND 999 THEN 9
                                 WHEN VALUE >= 1000 THEN 10
                              END
                                 VALUE
                         FROM t)
             GROUP BY state)
    SELECT STATE,
           "<100",
           "100-199",
           "200-299",
           "300-399",
           "400-499",
           "500-599",
           "600-699",
           "700-799",
           "800-899",
           "900-999",
           ">=1000",
             "<100"
           + "100-199"
           + "200-299"
           + "300-399"
           + "400-499"
           + "500-599"
           + "600-699"
           + "700-799"
           + "800-899"
           + "900-999"
           + ">=1000"
              total
      FROM t1;
    Cheers,
    Manik.
  • 8. Re: How to use Pivot function for group range in oracle SQL
    981984 Newbie
    Currently Being Moderated
    This solutions is useful and Thanks for your reply.

    How can i get the Min value and Max value for each row ?

    State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total     Min     Max
    IL     0     0     1     5     1     5     40     1     1     40     0     53     $10     $2,500
    IN     0     0     0     0     0     0     1     49     49     1     0     3     $70     $1,500
  • 9. Re: How to use Pivot function for group range in oracle SQL
    Manik Expert
    Currently Being Moderated
    Can you define min and max.. in what context you want it?

    Cheers,
    Manik.
  • 10. Re: How to use Pivot function for group range in oracle SQL
    jeneesh Guru
    Currently Being Moderated
    user8564931 wrote:
    This solutions is useful and Thanks for your reply.

    How can i get the Min value and Max value for each row ?

    State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total     Min     Max
    IL     0     0     1     5     1     5     40     1     1     40     0     53     $10     $2,500
    IN     0     0     0     0     0     0     1     49     49     1     0     3     $70     $1,500
    This?
    WITH t AS
            (SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 67 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 23 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 78 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 34 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 4 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 12 VALUE FROM DUAL
             UNION ALL
             SELECT 'AL' state, 15 VALUE FROM DUAL
             UNION ALL
             SELECT 'AZ' state, 6 VALUE FROM DUAL
             UNION ALL
             SELECT 'AZ' state, 123 VALUE FROM DUAL
             UNION ALL
             SELECT 'AZ' state, 123 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 23 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 120 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 456 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 11 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 24 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 34 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 87 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 23 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 234 VALUE FROM DUAL
             UNION ALL
             SELECT 'MA' state, 789 VALUE FROM DUAL
             UNION ALL
             SELECT 'MH' state, 54321 VALUE FROM DUAL),
         -- End of test data
         t1 AS
            (  SELECT state,
                      NVL (COUNT (DECODE (VALUE, 0, 0)), 0) "<100",
                      NVL (COUNT (DECODE (VALUE, 1, 1)), 0) "100-199",
                      NVL (COUNT (DECODE (VALUE, 2, 2)), 0) "200-299",
                      NVL (COUNT (DECODE (VALUE, 3, 3)), 0) "300-399",
                      NVL (COUNT (DECODE (VALUE, 4, 4)), 0) "400-499",
                      NVL (COUNT (DECODE (VALUE, 5, 5)), 0) "500-599",
                      NVL (COUNT (DECODE (VALUE, 6, 6)), 0) "600-699",
                      NVL (COUNT (DECODE (VALUE, 7, 7)), 0) "700-799",
                      NVL (COUNT (DECODE (VALUE, 8, 8)), 0) "800-899",
                      NVL (COUNT (DECODE (VALUE, 9, 9)), 0) "900-999",
                      NVL (COUNT (DECODE (VALUE, 10, 10)), 0) ">=1000"
                 FROM (SELECT state,
                              CASE
                                 WHEN VALUE < 100 THEN 0
                                 WHEN VALUE BETWEEN 100 AND 199 THEN 1
                                 WHEN VALUE BETWEEN 200 AND 299 THEN 2
                                 WHEN VALUE BETWEEN 300 AND 399 THEN 3
                                 WHEN VALUE BETWEEN 400 AND 499 THEN 4
                                 WHEN VALUE BETWEEN 500 AND 599 THEN 5
                                 WHEN VALUE BETWEEN 600 AND 699 THEN 6
                                 WHEN VALUE BETWEEN 700 AND 799 THEN 7
                                 WHEN VALUE BETWEEN 800 AND 899 THEN 8
                                 WHEN VALUE BETWEEN 900 AND 999 THEN 9
                                 WHEN VALUE >= 1000 THEN 10
                              END
                                 VALUE
                         FROM t)
             GROUP BY state)
    SELECT STATE,
           "<100",
           "100-199",
           "200-299",
           "300-399",
           "400-499",
           "500-599",
           "600-699",
           "700-799",
           "800-899",
           "900-999",
           ">=1000",
             "<100"
           + "100-199"
           + "200-299"
           + "300-399"
           + "400-499"
           + "500-599"
           + "600-699"
           + "700-799"
           + "800-899"
           + "900-999"
           + ">=1000"
              total,
         least("<100",
           "100-199",
           "200-299",
           "300-399",
           "400-499",
           "500-599",
           "600-699",
           "700-799",
           "800-899",
           "900-999",
           ">=1000") min_val,
          greatest("<100",
           "100-199",
           "200-299",
           "300-399",
           "400-499",
           "500-599",
           "600-699",
           "700-799",
           "800-899",
           "900-999",
           ">=1000") max_val
      FROM t1
    /
  • 11. Re: How to use Pivot function for group range in oracle SQL
    981984 Newbie
    Currently Being Moderated
    Hi,

    I tried the solution, getting an issue with the min and max values. I had required the minimum value in the given range, where as the given solution for min and max is not working. Please help me on this.

    State     <100     100-199     200-299     300-399     400-499     500-599     600-699     700-799     800-899     900-999     >=1000     Total     Min     Max
    AK     1     2     0     4     1     4     4     35     35     4     1     25     *$85*     *$1,200*AL     0     0     2     27     10     17     35     2     2     35     0     103     *$100*     *$1,500*
  • 12. Re: How to use Pivot function for group range in oracle SQL
    981984 Newbie
    Currently Being Moderated
    I tried the solution and getting an issue with Min and Max values. I had required the Min and Max value in between ranges.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points