6 Replies Latest reply: Feb 17, 2014 4:59 AM by BluShadow RSS

    Display Data As Column Headings

    Gus C

      I am trying to achieve this in Apex 4.2

       

      My table is like (example)

       

      id              number

      date_a      date

      value_1     number

      value_2     number

      value_2     number

       

      I have my sql as

       

      select id, date_a, value_1, value_2, value_3

      from my_table

       

      So the report displays as

      id          date_a           value_1    value_2      value_3

      1           01/01/2012     10             20             30

      2           15/03/2012      12            14             24

       

      Can someone show me an example of how to create a report like

       

      01/01/2012         15/03/2012

          1                           2

         10                        12

         20                        14

         30                         24

       

      I had a look at pivot, but does not seem to be the thing

       

      Cheers

       

      Gus

        • 1. Re: Display Data As Column Headings
          Frank Kulash

          Hi, Gus,

          Gus C wrote:

           

          I am trying to achieve this in Apex 4.2

           

          My table is like (example)

           

          id              number

          date_a      date

          value_1     number

          value_2     number

          value_2     number

           

          I have my sql as

           

          select id, date_a, value_1, value_2, value_3

          from my_table

           

          So the report displays as

          id          date_a           value_1    value_2      value_3

          1           01/01/2012     10             20             30

          2           15/03/2012      12            14             24

           

          Can someone show me an example of how to create a report like

           

          01/01/2012         15/03/2012

              1                           2

             10                        12

             20                        14

             30                         24

           

          I had a look at pivot, but does not seem to be the thing

           

          Cheers

           

          Gus

          Column aliases have to be hard-coded into the query.  If you want the column aliases to be based on data from the result set, then you need dynamic SQL.   Your front end may have ways of making this easy.  for example, in SQL*Plus you can use COLUMN ... NEW_VALUE to set substitution variables, and use those variables as column aliases.  Sorry, I don't know much about Apex.  Perhaps you might be better off closing this thread and starting a new question in the Apex forum:

          https://community.oracle.com/community/developer/english/oracle_database/application_express

           

          Another alternative is to use produce an extra row at the beginning of the result set that contains the column alias.  (The generic real headers will probably be distracting if not misleading.  Again, your front end can probably help; it generally has ways to suppress headers.)

          • 2. Re: Display Data As Column Headings
            mihai.manolescu

            Take a look into PL/SQL Funtion for the SQL of the report not sure if it is what you're looking for or if it is the best example but it works:

             

            Thanks

             

            declare

              v_day varchar2(4000);

              l_q CHAR(1) := '''';

            begin

            for cell in (

            select 1 dummy, to_char(sysdate,'DD-MON-YYYY') date_a from dual

            union

            select 2 dummy, to_char(sysdate+45,'DD-MON-YYYY') date_a from dual

            ) loop

                v_day := v_day||l_q||cell.date_a||l_q||' as "'||cell.date_a||'",';

            end loop;

            v_day := substr(v_day,1,length(v_day)-1);

            v_day := ('select * from

            (select 1 dummy, to_char(sysdate,''DD-MON-YYYY'') date_a from dual

            union

            select 2 dummy, to_char(sysdate+45,''DD-MON-YYYY'') date_a from dual) s pivot (sum(dummy)

              for date_a in ('||v_day||'))');

              return v_day;

            end;

            • 3. Re: Display Data As Column Headings
              Gus C

              Can anybody else help with this, as at the moment, I am getting nowhere

               

              Gus

              • 4. Re: Display Data As Column Headings
                mihai.manolescu

                Hey Gus,

                 

                what do you mean you are getting nowhere? The function returning the pivot SQL is exactly what you need if i read your question right. You just need to adapt it to your table.

                 

                Take a look at eg: Column Heading as date where i have just copied the function above into a report region.

                 

                Thanks.

                • 5. Re: Display Data As Column Headings
                  Gus C

                  As a test I am trying to adapt it to the emp table.

                  empno, ename and hiredate

                   

                  So my finished report should be something like

                   

                  01/02/2004       15/04/2005      26/11/2005

                  123                    456                    789

                  Jones               Smith               Davis

                  • 6. Re: Display Data As Column Headings
                    BluShadow

                    Gus,

                     

                    Firstly you need to understand that SQL itself cannot change the column names for a query, as Frank said.

                    The columns of an SQL query are the 'projection' and the number of columns, the names of them, and the datatypes have to be determined before any data is fetched.

                     

                    See this article: PL/SQL 101 : Cursors and SQL Projection

                     

                    Apex is simply based around the queries you write, and likewise it will not magically be able to change report column names based on your data.

                     

                    That isn't to say what you want is impossible... it can be done... just not easily...

                     

                    see: How to pipeline a function with a dynamic number of columns?

                     

                    Essentially the data itself has to be queried or known, before the data is fetched, so that requires either the columns being dynamically generated based on some known data (e.g. criteria entered by a user) or you will have to do a two pass query... querying the data once to get the values and then dynamically generating a second query to fetch the data with those dynamically generated column headings.

                    In Apex reports you will generally have the problem that the Report Attributes are defined as part of the development and are determined when the query is assigned to the report (though you can manually override the headings)... but apex will not allow for the headings to dynamically change based on the data, for any of the standard reports it allows.  I've not tried to do it in Apex, but you could perhaps try and generate the results of the report using a two pass method, to put into an HTML region, but then it depends what user interaction is required with the report, as you're going away from the built in reporting features.