Gus C wrote:
I am trying to achieve this in Apex 4.2
My table is like (example)
I have my sql as
select id, date_a, value_1, value_2, value_3
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
I had a look at pivot, but does not seem to be the thing
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:
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.)
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:
l_q CHAR(1) := '''';
for cell in (
select 1 dummy, to_char(sysdate,'DD-MON-YYYY') date_a from dual
select 2 dummy, to_char(sysdate+45,'DD-MON-YYYY') date_a from dual
v_day := v_day||l_q||cell.date_a||l_q||' as "'||cell.date_a||'",';
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
select 2 dummy, to_char(sysdate+45,''DD-MON-YYYY'') date_a from dual) s pivot (sum(dummy)
for date_a in ('||v_day||'))');
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.
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...
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.