G'day

After some further reading, would investigating pivot queries be of any use?
Could it apply in this case?

Thanks.

hi tom,

can you please help me with the query.


CREATE TABLE TABL_RESULTDATA
(
Loadpoints VARCHAR2(125)
, LoadPointSteps VARCHAR2(125)
, Variable VARCHAR2(125)
, Step NUMBER
, Value NUMBER
, VariableType VARCHAR2(20)
)
/

insert into tabl_resultdata values('HA_000_LP_000','S_001', 'AD1Out(ADcard_1)',1,5, 'SET')
/
insert into tabl_resultdata values('HA_000_LP_000','S_001','N',1,100,'SET')
/
insert into tabl_resultdata values('HA_000_LP_000','S_001','Load(DDE)',1,1,'SET')
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','Q_quer(Emi2)',1,9.5,'MEAS')
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','T_I(Emi2)',1,0,'MEAS')
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','AD1Out(ADcard_1)',1,0,'SET')
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','N',1,100,'SET')
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','Load(DDE)',1,1,'SET')
/
insert into tabl_resultdata values('HA_000_LP_001','S_001','Q_Ueber(KMM)',1,49.83,'MEAS')
/
insert into tabl_resultdata values('HA_000_LP_001','S_001','T_Volkor(KMM)',1,0,'MEAS')
/
insert into tabl_resultdata values('HA_000_LP_001','S_001','C1(LeCroy)',1,0,'MEAS')
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1Out(ADcard_1)',1,10,'SET')
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1Out(ADcard_1)',2,12,'SET')
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1Out(ADcard_1)',3,12,'SET')
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1(ADcard_1)',1,9.025,'MEAS')
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1(ADcard_1)',2,9.05,'MEAS')
/

i want the result in such a way.

all distinct variables should be in horizontal columns.
all distinct loadpoints vertical.
loadpoints will repeat vetically if it has more than one loadpointsteps.
loadpoints will also repeat vetically if it has more than one steps.

then based on that each variable value should be represented as shown below.


AD1Out(ADcard_1) N Load(DDE) Q_quer(Emi2) T_I(Emi2) Q_Ueber(KMM) T_Volkor(KMM) C1(LeCroy) AD1(ADcard_1)

HA_000_LP_000 S_001 1 5 100 1
HA_000_LP_000 S_002 1 0 100 1 9.5 0
HA_000_LP_001 S_001 1 49 0 0
HA_000_LPSCHL_000 S_001 1 10 9.025
HA_000_LPSCHL_000 S_001 2 12 9.05
HA_000_LPSCHL_000 S_001 3 12


thanks .

hi tom ,

from your site i found how to build the query dynamically and got the results.

create or replace package body pivot
as
procedure data( p_cursor in out rc )
is
l_stmt long;
l_index number := 0;
begin
l_stmt := 'select loadpoints, loadpointsteps ,step ';
for x in ( select distinct variable from tabl_resultdata order by 1 )
loop
l_index := l_index + 1;
-- l_stmt := l_stmt ||', max(decode(variable,' || ''''|| x.variable ||''''||', value )) ' ||replace(replace(x.variable, '('), ')');
l_stmt := l_stmt ||', max(decode(variable,' || ''''|| x.variable ||''''||', value )) VALUE_'||l_index ;
end loop;
l_stmt := l_stmt || ' from tabl_Resultdata group by loadpoints, loadpointsteps, step order by loadpoints';
open p_cursor for l_stmt;
end;
end;
/

column loadpoints format a17
column loadpointsteps format a5
column step format 999
column value_1 format 999.999
column value_2 format 999.999
column value_3 format 999.999
column value_4 format 999.999
column value_5 format 999.999
column value_6 format 999.999
column value_7 format 999.999
column value_8 format 999.999
column value_9 format 999.999


variable x refcursor
set autoprint on
exec pivot.data( :x );

but now if i have to loop through the ref cursor and write to a file instead of executing in the sql*plus how can i reference the column names from ref cursor becuase it is not known to me.


maybe i am making a silly mistake.
pls help me out.

hi tom,

thanks a lot. i could do it as told by you. i wrote exec_sql in forms and i am able to write the contents into excel and also the column names.

now i have a new requirement.

here is the testcase , it runs for me and hope u can also run this.

CREATE TABLE TABL_RESULTDATA
(
Loadpoints VARCHAR2(125)
, LoadPointSteps VARCHAR2(125)
, Variable VARCHAR2(125)
, Step NUMBER
, Value NUMBER
, VariableType VARCHAR2(20)
, ReadBackValue NUMBER
)
/

insert into tabl_resultdata values('HA_000_LP_000','S_001', 'AD1Out(ADcard_1)',1,5, 'SET', 10)
/
insert into tabl_resultdata values('HA_000_LP_000','S_001','N',1,100,'SET', 10)
/
insert into tabl_resultdata values('HA_000_LP_000','S_001','Load(DDE)',1,1,'SET', null)
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','Q_quer(Emi2)',1,9.5,'MEAS', null)
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','T_I(Emi2)',1,0,'MEAS', null)
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','AD1Out(ADcard_1)',1,0,'SET', null)
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','N',1,100,'SET', null)
/
insert into tabl_resultdata values('HA_000_LP_000','S_002','Load(DDE)',1,1,'SET', null)
/
insert into tabl_resultdata values('HA_000_LP_001','S_001','Q_Ueber(KMM)',1,49.83,'MEAS', null)
/
insert into tabl_resultdata values('HA_000_LP_001','S_001','T_Volkor(KMM)',1,0,'MEAS', null)
/
insert into tabl_resultdata values('HA_000_LP_001','S_001','C1(LeCroy)',1,0,'MEAS', null)
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1Out(ADcard_1)',1,10,'SET', null)
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1Out(ADcard_1)',2,12,'SET', 17)
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1Out(ADcard_1)',3,12,'SET', 19)
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1(ADcard_1)',1,9.025,'MEAS', null)
/
insert into tabl_resultdata values('HA_000_LPSCHL_000','S_001','AD1(ADcard_1)',2,9.05,'MEAS', null)
/


create or replace package pivot
as
type rc is ref cursor;
procedure data(p_cursor in out rc);
end;
/


create or replace package body pivot
as
procedure data( p_cursor in out rc )
is
l_stmt long;
l_index number := 0;
begin
l_stmt := 'select loadpoints, loadpointsteps ,step ';
for x in ( select distinct variable, variabletype from tabl_resultdata order by 2 )
loop
l_index := l_index + 1;
l_stmt := l_stmt ||', max(decode(variable,' || ''''|| x.variable||''''||', value )) ' ||replace(replace(x.variable||'('||x.variabletype||')', '('), ')');
end loop;
l_stmt := l_stmt || ' from tabl_Resultdata group by loadpoints,loadpointsteps, step order by loadpoints';
open p_cursor for l_stmt;
end;
end;
/

set linesize 198
column loadpoints format a17
column loadpointsteps format a5
column step format 999
column value_1 format 999.999
column value_2 format 999.999
column value_3 format 999.999
column value_4 format 999.999
column value_5 format 999.999
column value_6 format 999.999
column value_7 format 999.999
column value_8 format 999.999
column value_9 format 999.999


variable x refcursor
set autoprint on
exec pivot.data( :x );


By requirement is, the last column ReadBackValue is only applicable for variabletype='SET' and it is not applicable for variabletype='MEAS'.

So in my display i want to have another column ReadBackValue next to the variable name and display the value. But the columns should be displayed only for SET variable types and not for MEAS variable types.
i tried to change the query and i could reach only that, i get the readbackvalue column for each variable.

something like

loadpoints loadpointsteps step AD1Out(ADcard_1)(SET)ReadBackValue N (SET) ReadBackValue Q_quer(Emi2)(MEAS) T_I(Emi2)(MEAS).....

Can you please help me out with the query or to achieve in pl/sql as anyhow i use pl/sql to write the contents into excel sheet. so no limitation.

thanks.

Hi Tom,

I have a requirement like this.

i have one table which has agreement information and another table which has service information. i have to join these two tables and for a particular product, i have to find out which all service are there as a single record. The problem is for a particular product, we can have many services involved and so the no. of columns has to be decided dynamically. even this is fine. The main probelm is the different column names should be different service_name.
Say for eg. for a product, i have 3 services namely A,B,C then i should have 3 columns and the names should be A,B and C.
There are 2 approaches as you have mentioned. One using Sql and Another using Pl/Sql.
If i have to use Pl/Sql, then i have to create a table(this table will be used for reporting) with these many(346) columns and if a particular service exist, then the values will be 'Yes'. But i dont think having 346 columns is a good idea.
So can you suggest any method where we can do the same using sql?

For the above post, Oracle version is 9.2.0.8

Hi tom,
For the above problem, i got the solution. i used the same procedure which was given above with modifications.Now i am able to display all the column names. But still there is one problem. In that procedure, the cursor was passed as a parameter. Is there any way we can avoid this? Because this procedure will be used an a reporting tool and i m not sure we can pass cursor as parameters there. So any ideas?

Hi,

SCOTT@orcl> ed
Wrote file afiedt.buf

1 create or replace procedure p_dynamic_sql
2 is
3 v_sql varchar2(4000);
4 begin
5 v_sql := 'select ';
6 for r in (select column_name
7 from user_tab_cols
8 where table_name = 'EMP'
9 and column_name not in ('EMPNO','JOB')) loop
10 v_sql := v_sql || r.column_name || ',';
11 end loop;
12 v_sql := rtrim(v_sql, ',') || ' from emp';
13 dbms_output.put_line( v_sql );
14 -- do something with SQL (open cursor, etc.)
15* end;
SCOTT@orcl> /

Procedure created.

SCOTT@orcl> set serveroutput on
SCOTT@orcl> exec p_dynamic_sql
select ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO from emp

PL/SQL procedure successfully completed.

1. How can i get the data as query output of this proecedure ?
2. If i wish to tell the name of columns at execution time of the procedure i.e. dynamic; how it can be possible?
Thanks

Hi,

Thanks for your reply.

>what is your goal with this routine.

I have a table in which date,veh1,veh2,veh3,veh4....veh100 are the columns; i.e. vehicle1, vehicle2 etc. Everyday, in these 100 vehicles; our driver fills the fuel and submit the petrol bill for their vehicle. Normally, all vehicle goes for trip everyday; except 1 or 2 (may be driver is on leave or vehicle is under repair). So in the application, we feed the petrol bill as we receive at random.

After feeding the petrol bill data, i have to get the select query of only those vehicles; in which today's amount is not null or zero or by other mean (store department) i comes to know that such and such vehicle number were not went on trip i.e. vehicle no. 52 and 73. So, i have to write everyday select query like select date, veh1,veh2...veh51,veh53...veh72,veh74...veh100 from veh.

So rather mentioning all the columns name, i wish to mention only those column names which vehicle number were not went on trip today; in the select query.

>Because it isn't a good idea in general.

Please guide me, how by other mean i can set the random columns in the select query.

Thanks & Regards.

create view v as
with data as (select level l from dual connect by level <= 10 )
select dt,
       'veh' || l veh_name,
       case when l = 1 then veh1
            when l = 2 then veh2
            ...
            when l = 100 then veh100
        end veh_value
  from data, t;

ops$tkyte%ORA9IR2> declare
  2          l_sql long := 'create table t ( dt date';
  3  begin
  4          for i in 1 .. 100
  5          loop
  6                  l_sql := l_sql || ', veh' || i || ' int';
  7          end loop;
  8          execute immediate l_sql || ')';
  9
 10          l_sql := '
 11  create view v
 12  as
 13  with data as (select level l from dual connect by level <= 10 )
 14  select dt,
 15         ''veh'' || l veh_name,
 16             case ';
 17
 18          for i in 1 .. 100
 19          loop
 20                  l_sql := l_sql || ' when l = ' || i || ' then veh' || i;
 21          end loop;
 22          execute immediate l_sql || ' end veh_value from data, t';
 23  end;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t ( dt, veh1, veh2, veh3  ) values ( trunc(sysdate), 0, 1, 2 );

1 row created.

Elapsed: 00:00:00.01
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from v where dt = trunc(sysdate) and nvl(veh_value,0) <> 0;

DT        VEH_NAME                                     VEH_VALUE
--------- ------------------------------------------- ----------
27-NOV-07 veh2                                                 1
27-NOV-07 veh3                                                 2

Elapsed: 00:00:00.02
ops$tkyte%ORA9IR2>

ops$tkyte%ORA9IR2> create index t_idx on t(dt);

Index created.

Elapsed: 00:00:00.01
ops$tkyte%ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows=> 10000, numblks => 1000 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
ops$tkyte%ORA9IR2> @at
ops$tkyte%ORA9IR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from v where dt = trunc(sysdate) and nvl(veh_value,0) <> 0;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5 Bytes=225)
   1    0   VIEW OF 'V' (Cost=4 Card=5 Bytes=225)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=5 Bytes=500)
   3    2       NESTED LOOPS (Cost=4 Card=5 Bytes=565)
   4    3         VIEW (Cost=2 Card=1 Bytes=13)
   5    4           CONNECT BY (WITHOUT FILTERING)
   6    5             TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)
   7    3         INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=40)



ops$tkyte%ORA9IR2> @atoff
ops$tkyte%ORA9IR2> set autotrace off

Hello,

I think you've got a fundamentally wrong schema/table design. Instead of trying to dynamically construct the query you should refactor your schema:
as I understand it, you've got table like
create table fuel_consuption (
[id number,]
day date,
veh1 number,
veh2 number,
....
veh100 number));

A better approach is to have a table like
create table fuel_consumption (
id number,
day date,
vehicle_id number,
vehicle_value number);

So one day's report gets saved into 100 rows of a 4 column table instead of into one row of a 102 column table.

To get only those vehicles of a particular day with existing data you use:
select day, vehicle_id, vehicle_value from
fuel_consumption where day = :some_day

Benefits of this approach over yours are (at minumum):
1. easiness (much shorter queries/table)
2. flexibility (you can add now a 101th vehicle just by adding an id (hopefully restricted to a child table)

I believe the process just described is called table/schema normalization. Google it :).

Your original request could be fullfied by using 'execute immediate' and probably 'open cursor for' try plsql documentation for these.

Hello,

>I think you've got a fundamentally wrong schema/table design.

Yes, you are right, but at this moment i do'nt want to change any structure; because it will require application re-designing and common solution to existing system. By the end of year, i wish to take changes.

Right now, if i would able to get the data with excluding those columns; in which amount is null or zero or a certain column names; then it will smooth my working and time saving too. Something like:
SCOTT@orcl> exec <someprocedure>
Enter Number of Vehicles not had trip:45,73

Date Veh1 Veh2 Veh3.. Veh44 Veh46.. Veh72 Veh74.. Veh100
....

Thanks & Regards

Hello Sir,

My Sincere thanks for your reply and efforts.
Finally i got the following function code which is something like this: (only one column will be excluded)

create or replace function dynamic_col_sel (pColumn varchar2,pTable varchar2)
return sys_refcursor as
c sys_refcursor;
v_sql varchar2(4000);
begin
v_sql := 'select ';
for r in (select column_name
from user_tab_cols
where table_name = pTable
and column_name not in (pColumn)) loop
v_sql := v_sql || r.column_name || ',';
end loop;
v_sql := rtrim(v_sql, ',') || ' From ' || pTable;
open c for v_sql;
return c;
end;

SYS@orcl>set serveroutput on
SYS@orcl>var c refcursor
SYS@orcl>execute :c := dynamic_col_sel('VEH73','T');
SYS@orcl>print c;

Now it is showing all the columns except VEH73; (here column name should be in UPPER case only).

Once again....
Thanks and warm Regards
Girish Kumar Sharma
Ajmer (India)

Hello Tom,

thank you for this site, I have learned a lot.

q:
<quote>
i give up.

totally.

i give up.

as I say to my kids "osh kosh by gosh"...


just giving up...................................
</quote>

Do you swear or laugh before answering like this? I've laughed several times during reading of this thread.

We apologize on behalf of Mr.Girish.

Here is the data...

Row# CUST_ID ELE_NAME COUNT
1 126 ACC 5
2 126 COM 15
3 126 COJ 10
4 126 COS 35

Now the requierment is to create query where the ELE_NAME values will be populated as Column Name and ELE_VALUE will be value for dynamic column names... We cant Hard Code ELE_NAME bcoz values might change...

The View should populate the data in the following way

Table Column Names : ACC CON COJ COS
Table Values: 5 15 10 35

Here is the data...

Row# CUST_ID ELE_NAME COUNT
1 126 ACC 5
2 126 COM 15
3 126 COJ 10
4 126 COS 35

Now the requierment is to create query where the ELE_NAME values will be populated as Column Name and ELE_VALUE will be value for dynamic column names... We cant Hard Code ELE_NAME bcoz values might change...

The View should populate the data in the following way

Table Column Names : ACC CON COJ COS
Table Values: 5 15 10 35

Forgive me, but I have been searching many threads here and still do not understand how to generate column names dynamically using pivot clause. I'm on 11gR1.

CREATE TABLE "PIVOT_EXAMPLE"
  (
    "EMP"   VARCHAR2(3),
    "PAY_CODE_ID" VARCHAR2(3),
    "HOURS"       NUMBER
  );

Insert into PIVOT_EXAMPLE (EMP,PAY_CODE_ID,HOURS) values ('ANN','PC4',8.5);
Insert into PIVOT_EXAMPLE (EMP,PAY_CODE_ID,HOURS) values ('ANN','PC2',8.5);
Insert into PIVOT_EXAMPLE (EMP,PAY_CODE_ID,HOURS) values ('BOB','PC5',8.5);
Insert into PIVOT_EXAMPLE (EMP,PAY_CODE_ID,HOURS) values ('BOB','PC0',8.5);
Insert into PIVOT_EXAMPLE (EMP,PAY_CODE_ID,HOURS) values ('JIM','PC5',8.5);
commit;

I understand the pivot:

with pivot_data as 
(select * from pivot_example)
 select * from pivot_data
    PIVOT (
              SUM(hours) 
       FOR PAY_CODE_ID
IN ('PC4','PC2','PC5','PC0'));

Which yields:

EMP  'PC4'  'PC2'  'PC5'  'PC0'
ANN  8.5    8.5
BOB                8.5    8.5
JIM                8.5 

But I would like to know how to compute the column names based on whatever values are in the table. I would also like to know how to combine pivot with rollup so I can have a totals row, for example:

EMP  'PC4'  'PC2'  'PC5'  'PC0'
ANN  8.5    8.5
BOB                8.5    8.5
JIM                8.5 

TOT  8.5    8.5    17     8.5

Thank you very much in advance.

Tom,
We can use the xml option to avoid making the query dynamic right?
But the problem is the result set is an xml.
Is there any way to convert that xml into a tabular format or result set something like

select * /*decode xml as tabular format??*/ from(
SELECT *
FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code 
                     FROM pivot_test 
                    WHERE  id < 10))
);