This discussion is locked
6 Replies Latest reply: Feb 17, 2014 2:59 AM by BluShadow RSS

Display Data As Column Headings

Gus C Journeyer
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

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

     

    Gus

  • 4. Re: Display Data As Column Headings
    mihai.manolescu Explorer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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.

Legend

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