12 Replies Latest reply: Jan 7, 2013 5:07 AM by 981984 RSS

    How to use Pivot function for group range in oracle SQL

    981984
      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_
          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
            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
              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_
                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
                  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_
                    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
                      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
                        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
                          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
                            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
                              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
                                I tried the solution and getting an issue with Min and Max values. I had required the Min and Max value in between ranges.