Oracle SQL transpose using dynamic column names

Question

I have a simple problem that I need to solve quickly. Would you please lend me your help?

The problem is as fOllows: I have two tables, one definition table and one history table.

create table revenue history(operation_date date, revenue_id number, total_revenue number); 
create table revenue_definition(revenue_id number, revenue_name varchar2(100));

insert into revenue_definition values(1,'Car');
insert into revenue_definition values(2,'Minivan');
insert into revenue_definition values(3,'Bus');

insert into revenue history values(sysdate-2,1,100);
insert into revenue history values(sysdate-2,2,150);
insert into revenue history values(sysdate-2,3,100);

insert into revenue history values(sysdate-1,1,200);
insert into revenue history values(sysdate-1,2,150);
insert into revenue history values(sysdate-1,3,200);

insert into revenue history values(sysdate,1,100);
insert into revenue history values(sysdate,2,150);
insert into revenue history values(sysdate,3,100);

Now what i need to do is show a report like this;

operation_date Car Minivan Bus
sysdate 100 150 100
sysdate-1 200 150 200
sysdate-2 100 150 100

I know that I can do this by using either decode or in 11g pivot. But for those I need to know the revenue types (Car,Minivan etc.) in advance and every time a definition is made I need to update my code. I want to avoid this if possible.

Any suggestions would be welcome,

Thanks.

user158017 · Accepted Answer · 2012-06-06 20:21:54Z

2

sounds like a candidate for dynamic sql. You'll need to actually build the sql statement as a string every time you run the query. You could build the string of column names by doing a select from the definition table, looping through the definitions and adding them to your sql statement, and then:

execute immediate 'sql statement'

There's no other way I know of to dynamically change which columns are displayed when the query runs.

answered Jun 6, 2012 at 20:21

user158017

3,0011 gold badge31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ersin Tozduman Over a year ago

Thanks a lot, but this doesn't help. I actually did this and it works. The problem is I am looking for a more maintainable way to do it. It's my mistake for not specifying in the question.

Tai Paul · Accepted Answer · 2012-10-16 15:19:07Z

This code runs in SQL*Plus on an Oracle 11g (11.2.0) database.

column dummy noprint
column "Header" format a14
column "Data"   format a40
SELECT 0 DUMMY
      ,'Operation Date' "Header"
      ,LISTAGG(lpad(' ', 9 - length(d.revenue_name) ,' ') || d.revenue_name ) WITHIN GROUP (ORDER BY d.revenue_id) "Data"
FROM  revenue_definition d
UNION
SELECT 1
      ,to_Char(h.operation_date,'DD-MON-YYYY')
      ,LISTAGG(lpad(' ',9 - length(h.total_revenue),' ') ||h.total_revenue) WITHIN GROUP (Order by d.revenue_id)
FROM revenue_definition d
    ,revenue_history    h
where h.revenue_id = d.revenue_id
group by h.operation_date
ORDER by 1, 2;

and produces this output:

Header           Data
-------------- ----------------------------------------
Operation Date       Car  Minivan      Bus
15-OCT-2012          100      150      100
16-OCT-2012          200      150      200
17-OCT-2012          100      150      100

John D · Accepted Answer · 2012-06-06 20:49:20Z

0

You can have a function pipe out the contents of definition as a single row - join that back to definition and history, should return what you want and adjust to data changes in definition.

An example: http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/tuning.htm#BABIEICA

answered Jun 6, 2012 at 20:49

John D

2,37517 silver badges29 bronze badges

1 Comment

Ersin Tozduman Over a year ago

I'm not really familiar with pipelined functions but I'll give it a try. Thanks for the help.

Collectives™ on Stack Overflow

Oracle SQL transpose using dynamic column names

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related