4 Replies Latest reply on Sep 25, 2017 3:22 PM by mathguy

    pivot and dynamic clause

    erdem ustun

      hi,

      I have used oracle 11.2.

      and I am working on it and trying to report it.there are hundreds of records processed on a daily.

       

      What I want to do is how many process have been performed on a daily basis.

      For example;

       

       

      I write sql and it works like I want it to work.no problem.

       

      with my_data as

      (select '01' as city_id, 'mail' as type, '01/01/2017' as  proc_date from dual union

      select '02' as city_id,  'phone' as type, '21/03/2017' as  proc_date from dual union

      select '03' as city_id, 'atm' as type, '12/03/2017'  as  proc_date from dual union

      select '04' as city_id, 'mail' as type, '12/03/2017'  as  proc_date from dual union

      select '05' as city_id, 'mail' as type, '01/01/2017' as  proc_date  from dual union

      select '06' as city_id, 'phone' as type, '01/01/2017' as  proc_date  from dual union

      select '07' as city_id, 'branch' as type, '20/09/2017' as  proc_date  from dual)

      select * from my_data

      pivot (count(city_id) for proc_date in ('01/01/2017','12/03/2017','21/03/2017','20/09/2017'))

       

      But whenever I want to get a report, I have to write the dates one by one for proc_date.

      To solve this, when I write sql as below, it gives an error ('ORA-00936: missing expression')

       

      with my_data as

      (select '01' as city_id, 'mail' as type, '01/01/2017' as  proc_date from dual union

      select '02' as city_id,  'phone' as type, '21/03/2017' as  proc_date from dual union

      select '03' as city_id, 'atm' as type, '12/03/2017'  as  proc_date from dual union

      select '04' as city_id, 'mail' as type, '12/03/2017'  as  proc_date from dual union

      select '05' as city_id, 'mail' as type, '01/01/2017' as  proc_date  from dual union

      select '06' as city_id, 'phone' as type, '01/01/2017' as  proc_date  from dual union

      select '07' as city_id, 'branch' as type, '20/09/2017' as  proc_date  from dual)

      select * from my_data

      pivot (count(city_id) for proc_date in (SELECT DISTINCT proc_date FROM my_data ORDER BY 1))

       

       

      It can be processed every day except holidays. Dynamically get dates instead of writing individual dates.

       

      I tried to use pivot xml but I could not create the format which  I wanted

      Untitled.jpg

      .

       

      I would like your help in this regard

        • 1. Re: pivot and dynamic clause
          Frank Kulash

          Hi,

          erdem ustun wrote:

           

          hi,

          I have used oracle 11.2.

          and I am working on it and trying to report it.there are hundreds of records processed on a daily.

           

          What I want to do is how many process have been performed on a daily basis.

          For example;

           

           

          I write sql and it works like I want it to work.no problem.

           

          with my_data as

          (select '01' as city_id, 'mail' as type, '01/01/2017' as proc_date from dual union

          ...

          '01/01/2017' is a string.  You should not be using a string to store DATE information, or trying to insert a string into a DATE column.  Use TO_DATE or DATE literals instead.

           

          ...

           

          But whenever I want to get a report, I have to write the dates one by one for proc_date.

          To solve this, when I write sql as below, it gives an error ('ORA-00936: missing expression')

           

          with my_data as

          (select '01' as city_id, 'mail' as type, '01/01/2017' as proc_date from dual union

          select '02' as city_id, 'phone' as type, '21/03/2017' as proc_date from dual union

          select '03' as city_id, 'atm' as type, '12/03/2017' as proc_date from dual union

          select '04' as city_id, 'mail' as type, '12/03/2017' as proc_date from dual union

          select '05' as city_id, 'mail' as type, '01/01/2017' as proc_date from dual union

          select '06' as city_id, 'phone' as type, '01/01/2017' as proc_date from dual union

          select '07' as city_id, 'branch' as type, '20/09/2017' as proc_date from dual)

          select * from my_data

          pivot (count(city_id) for proc_date in (SELECT DISTINCT proc_date FROM my_data ORDER BY 1))

           

           

          It can be processed every day except holidays. Dynamically get dates instead of writing individual dates.

           

          I tried to use pivot xml but I could not create the format which I wanted

          Untitled.jpg

          .

           

          I would like your help in this regard

          You can't do that with static SQL.  In static SQL, you must explicitly say how many columns will be in the result set and what those columns will be named.  If you want the number of columns and/or their names to depend on the data found, then you must use dynamic SQL.

           

          For work-arounds, see

          Re: Report count and sum from many rows into many columns

          PIVOT SQL

          • 2. Re: pivot and dynamic clause
            Mustafa KALAYCI

            as Frank said there is no dynamic definition for pivot clause but you can use pivot XML which let you add dynamic clauses but result is also an XML data

            examples: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

             

            there are some other options like ODCI but not necessary. you can also use a PLSQL block to generate dynamic sqls.

            • 3. Re: pivot and dynamic clause
              Paulzip

              You cannot do that with SQL, as columns have to be known at parse time, even with PIVOT and UNPIVOT.

               

              The best approach I know of to do what you want is found here, which uses the Oracle Data Cartridge Interface (ODCI) and dynamic SQL.

              • 4. Re: pivot and dynamic clause
                mathguy

                WHY do you want to do that?

                 

                Presenting data "in columns" instead of "in rows" is often a reporting requirement. If so, use your reporting tools to do the dynamic pivoting, there is no reason to write the dynamic SQL code by hand. Then in the reporting tool you will be able to format the column headers, align to the left or to the right, add page numbers to the report, etc. None of these should be done in SQL, they are all reporting issues. Use the right tool for them; and dynamic pivoting falls in the same category.

                 

                If you want to do it, not for the end result but only so that you can learn and practice dynamic pivoting, the Replies you received so far give you some excellent pointers.

                 

                If you think you need the data in that format for further processing (so you didn't ask the question for reporting purposes, and you didn't ask it because you want to learn and practice dynamic SQL, regardless of the end result), please share the business problem you are tying to address. There is a very high likelihood that there are better ways.

                 

                Cheers   -   mathguy