Solved

Oracle Crosstab query sql

Posted on 2009-04-06
10
2,515 Views
Last Modified: 2013-12-18
Hi

I am wanting to perform the equivilent of an Access Crosstab query using SQL in an Oracle 10G database.
My table looks like this:
CODE      PTYPE      NAME      PHONE
111      DOCTOR1      Smith      82145555
111      DOCTOR2      Jones      86547895
111      NURSE1      Green      84578978
111      NURSE2      Adams      5214 8877
111      OT1      Brown      2124 5588
111      AGENT1      Grey      8214 5587

I need the result to look have a single row of data with the columns named by joining the content of the PTYPE with the other column names for example the resulting table would contain columns DOCTOR1_NAME, DOCTOR1_PHONE, DOCTOR2_NAME, DOCTOR2_PHONE, NURSE1_NAME, NURSE1_PHONE etc etc etc

The number of different PTYPES could change depending on the data so I do not think using a DECODE is the way to go.

An example is in the attahced file.

Your help is greatly appreciated

the following code works but is limited to known data in the field called PREFIX
 
select nmatter, 
max(decode (prefix, 'BARRISTER1', search, null)) BARRISTER1_SEARCH, 
max(decode (prefix, 'BARRISTER2', search, null)) BARRISTER2_SEARCH,
max(decode (prefix, 'BARRISTER3', search, null)) BARRISTER3_SEARCH,
max(decode (prefix, 'BARRISTER4', search, null)) BARRISTER4_SEARCH,
max(decode (prefix, 'BARRISTER5', search, null)) BARRISTER5_SEARCH,
max(decode (prefix, 'BARRISTER6', search, null)) BARRISTER6_SEARCH,
max(decode (prefix, 'BARRISTER7', search, null)) BARRISTER7_SEARCH,
max(decode (prefix, 'BARRISTER8', search, null)) BARRISTER8_SEARCH,
max(decode (prefix, 'BARRISTER9', search, null)) BARRISTER9_SEARCH,
---
max(decode (prefix, 'DOCTOR1', search, null)) DOCTOR1_SEARCH, 
max(decode (prefix, 'DOCTOR2', search, null)) DOCTOR2_SEARCH,
max(decode (prefix, 'DOCTOR3', search, null)) DOCTOR3_SEARCH,
max(decode (prefix, 'DOCTOR4', search, null)) DOCTOR4_SEARCH,
max(decode (prefix, 'DOCTOR5', search, null)) DOCTOR5_SEARCH,
max(decode (prefix, 'DOCTOR6', search, null)) DOCTOR6_SEARCH,
max(decode (prefix, 'DOCTOR7', search, null)) DOCTOR7_SEARCH,
max(decode (prefix, 'DOCTOR8', search, null)) DOCTOR8_SEARCH,
max(decode (prefix, 'DOCTOR9', search, null)) DOCTOR9_SEARCH,
---
max(decode (prefix, 'DEFENDANT1', search, null)) DEFENDANT1_SEARCH, 
max(decode (prefix, 'DEFENDANT2', search, null)) DEFENDANT2_SEARCH,
max(decode (prefix, 'DEFENDANT3', search, null)) DEFENDANT3_SEARCH,
max(decode (prefix, 'DEFENDANT4', search, null)) DEFENDANT4_SEARCH,
max(decode (prefix, 'DEFENDANT5', search, null)) DEFENDANT5_SEARCH,
max(decode (prefix, 'DEFENDANT6', search, null)) DEFENDANT6_SEARCH,
max(decode (prefix, 'DEFENDANT7', search, null)) DEFENDANT7_SEARCH,
max(decode (prefix, 'DEFENDANT8', search, null)) DEFENDANT8_SEARCH,
max(decode (prefix, 'DEFENDANT9', search, null)) DEFENDANT9_SEARCH,
---
max(decode (prefix, 'EMPLOYER1', search, null)) EMPLOYER1_SEARCH, 
max(decode (prefix, 'EMPLOYER2', search, null)) EMPLOYER2_SEARCH,
max(decode (prefix, 'EMPLOYER3', search, null)) EMPLOYER3_SEARCH,
max(decode (prefix, 'EMPLOYER4', search, null)) EMPLOYER4_SEARCH,
max(decode (prefix, 'EMPLOYER5', search, null)) EMPLOYER5_SEARCH,
max(decode (prefix, 'EMPLOYER6', search, null)) EMPLOYER6_SEARCH,
max(decode (prefix, 'EMPLOYER7', search, null)) EMPLOYER7_SEARCH,
max(decode (prefix, 'EMPLOYER8', search, null)) EMPLOYER8_SEARCH,
max(decode (prefix, 'EMPLOYER9', search, null)) EMPLOYER9_SEARCH,
---
max(decode (prefix, 'GUSRDIAN1', search, null)) GUSRDIAN1_SEARCH, 
max(decode (prefix, 'GUSRDIAN2', search, null)) GUSRDIAN2_SEARCH,
max(decode (prefix, 'GUSRDIAN3', search, null)) GUSRDIAN3_SEARCH,
max(decode (prefix, 'GUSRDIAN4', search, null)) GUSRDIAN4_SEARCH,
max(decode (prefix, 'GUSRDIAN5', search, null)) GUSRDIAN5_SEARCH,
max(decode (prefix, 'GUSRDIAN6', search, null)) GUSRDIAN6_SEARCH,
max(decode (prefix, 'GUSRDIAN7', search, null)) GUSRDIAN7_SEARCH,
max(decode (prefix, 'GUSRDIAN8', search, null)) GUSRDIAN8_SEARCH,
max(decode (prefix, 'GUSRDIAN9', search, null)) GUSRDIAN9_SEARCH,
---
max(decode (prefix, 'INSURER1', search, null)) INSURER1_SEARCH, 
max(decode (prefix, 'INSURER2', search, null)) INSURER2_SEARCH,
max(decode (prefix, 'INSURER3', search, null)) INSURER3_SEARCH,
max(decode (prefix, 'INSURER4', search, null)) INSURER4_SEARCH,
max(decode (prefix, 'INSURER5', search, null)) INSURER5_SEARCH,
max(decode (prefix, 'INSURER6', search, null)) INSURER6_SEARCH,
max(decode (prefix, 'INSURER7', search, null)) INSURER7_SEARCH,
max(decode (prefix, 'INSURER8', search, null)) INSURER8_SEARCH,
max(decode (prefix, 'INSURER9', search, null)) INSURER9_SEARCH,
---
---
max(decode (prefix, 'BARRISTER1', NAME, null)) BARRISTER1_NAME, 
max(decode (prefix, 'BARRISTER2', NAME, null)) BARRISTER2_NAME,
max(decode (prefix, 'BARRISTER3', NAME, null)) BARRISTER3_NAME,
max(decode (prefix, 'BARRISTER4', NAME, null)) BARRISTER4_NAME,
max(decode (prefix, 'BARRISTER5', NAME, null)) BARRISTER5_NAME,
max(decode (prefix, 'BARRISTER6', NAME, null)) BARRISTER6_NAME,
max(decode (prefix, 'BARRISTER7', NAME, null)) BARRISTER7_NAME,
max(decode (prefix, 'BARRISTER8', NAME, null)) BARRISTER8_NAME,
max(decode (prefix, 'BARRISTER9', NAME, null)) BARRISTER9_NAME,
---
max(decode (prefix, 'DOCTOR1', NAME, null)) DOCTOR1_NAME, 
max(decode (prefix, 'DOCTOR2', NAME, null)) DOCTOR2_NAME,
max(decode (prefix, 'DOCTOR3', NAME, null)) DOCTOR3_NAME,
max(decode (prefix, 'DOCTOR4', NAME, null)) DOCTOR4_NAME,
max(decode (prefix, 'DOCTOR5', NAME, null)) DOCTOR5_NAME,
max(decode (prefix, 'DOCTOR6', NAME, null)) DOCTOR6_NAME,
max(decode (prefix, 'DOCTOR7', NAME, null)) DOCTOR7_NAME,
max(decode (prefix, 'DOCTOR8', NAME, null)) DOCTOR8_NAME,
max(decode (prefix, 'DOCTOR9', NAME, null)) DOCTOR9_NAME,
---
max(decode (prefix, 'DEFENDANT1', NAME, null)) DEFENDANT1_NAME, 
max(decode (prefix, 'DEFENDANT2', NAME, null)) DEFENDANT2_NAME,
max(decode (prefix, 'DEFENDANT3', NAME, null)) DEFENDANT3_NAME,
max(decode (prefix, 'DEFENDANT4', NAME, null)) DEFENDANT4_NAME,
max(decode (prefix, 'DEFENDANT5', NAME, null)) DEFENDANT5_NAME,
max(decode (prefix, 'DEFENDANT6', NAME, null)) DEFENDANT6_NAME,
max(decode (prefix, 'DEFENDANT7', NAME, null)) DEFENDANT7_NAME,
max(decode (prefix, 'DEFENDANT8', NAME, null)) DEFENDANT8_NAME,
max(decode (prefix, 'DEFENDANT9', NAME, null)) DEFENDANT9_NAME,
---
max(decode (prefix, 'EMPLOYER1', NAME, null)) EMPLOYER1_NAME, 
max(decode (prefix, 'EMPLOYER2', NAME, null)) EMPLOYER2_NAME,
max(decode (prefix, 'EMPLOYER3', NAME, null)) EMPLOYER3_NAME,
max(decode (prefix, 'EMPLOYER4', NAME, null)) EMPLOYER4_NAME,
max(decode (prefix, 'EMPLOYER5', NAME, null)) EMPLOYER5_NAME,
max(decode (prefix, 'EMPLOYER6', NAME, null)) EMPLOYER6_NAME,
max(decode (prefix, 'EMPLOYER7', NAME, null)) EMPLOYER7_NAME,
max(decode (prefix, 'EMPLOYER8', NAME, null)) EMPLOYER8_NAME,
max(decode (prefix, 'EMPLOYER9', NAME, null)) EMPLOYER9_NAME,
---
max(decode (prefix, 'GUSRDIAN1', NAME, null)) GUSRDIAN1_NAME, 
max(decode (prefix, 'GUSRDIAN2', NAME, null)) GUSRDIAN2_NAME,
max(decode (prefix, 'GUSRDIAN3', NAME, null)) GUSRDIAN3_NAME,
max(decode (prefix, 'GUSRDIAN4', NAME, null)) GUSRDIAN4_NAME,
max(decode (prefix, 'GUSRDIAN5', NAME, null)) GUSRDIAN5_NAME,
max(decode (prefix, 'GUSRDIAN6', NAME, null)) GUSRDIAN6_NAME,
max(decode (prefix, 'GUSRDIAN7', NAME, null)) GUSRDIAN7_NAME,
max(decode (prefix, 'GUSRDIAN8', NAME, null)) GUSRDIAN8_NAME,
max(decode (prefix, 'GUSRDIAN9', NAME, null)) GUSRDIAN9_NAME,
---
max(decode (prefix, 'INSURER1', NAME, null)) INSURER1_NAME, 
max(decode (prefix, 'INSURER2', NAME, null)) INSURER2_NAME,
max(decode (prefix, 'INSURER3', NAME, null)) INSURER3_NAME,
max(decode (prefix, 'INSURER4', NAME, null)) INSURER4_NAME,
max(decode (prefix, 'INSURER5', NAME, null)) INSURER5_NAME,
max(decode (prefix, 'INSURER6', NAME, null)) INSURER6_NAME,
max(decode (prefix, 'INSURER7', NAME, null)) INSURER7_NAME,
max(decode (prefix, 'INSURER8', NAME, null)) INSURER8_NAME,
max(decode (prefix, 'INSURER9', NAME, null)) INSURER9_NAME,
---
---
max(decode (prefix, 'BARRISTER1', ADDRESS, null)) BARRISTER1_ADDRESS, 
max(decode (prefix, 'BARRISTER2', ADDRESS, null)) BARRISTER2_ADDRESS,
max(decode (prefix, 'BARRISTER3', ADDRESS, null)) BARRISTER3_ADDRESS,
max(decode (prefix, 'BARRISTER4', ADDRESS, null)) BARRISTER4_ADDRESS,
max(decode (prefix, 'BARRISTER5', ADDRESS, null)) BARRISTER5_ADDRESS,
max(decode (prefix, 'BARRISTER6', ADDRESS, null)) BARRISTER6_ADDRESS,
max(decode (prefix, 'BARRISTER7', ADDRESS, null)) BARRISTER7_ADDRESS,
max(decode (prefix, 'BARRISTER8', ADDRESS, null)) BARRISTER8_ADDRESS,
max(decode (prefix, 'BARRISTER9', ADDRESS, null)) BARRISTER9_ADDRESS,
---
max(decode (prefix, 'DOCTOR1', ADDRESS, null)) DOCTOR1_ADDRESS, 
max(decode (prefix, 'DOCTOR2', ADDRESS, null)) DOCTOR2_ADDRESS,
max(decode (prefix, 'DOCTOR3', ADDRESS, null)) DOCTOR3_ADDRESS,
max(decode (prefix, 'DOCTOR4', ADDRESS, null)) DOCTOR4_ADDRESS,
max(decode (prefix, 'DOCTOR5', ADDRESS, null)) DOCTOR5_ADDRESS,
max(decode (prefix, 'DOCTOR6', ADDRESS, null)) DOCTOR6_ADDRESS,
max(decode (prefix, 'DOCTOR7', ADDRESS, null)) DOCTOR7_ADDRESS,
max(decode (prefix, 'DOCTOR8', ADDRESS, null)) DOCTOR8_ADDRESS,
max(decode (prefix, 'DOCTOR9', ADDRESS, null)) DOCTOR9_ADDRESS,
---
max(decode (prefix, 'DEFENDANT1', ADDRESS, null)) DEFENDANT1_ADDRESS, 
max(decode (prefix, 'DEFENDANT2', ADDRESS, null)) DEFENDANT2_ADDRESS,
max(decode (prefix, 'DEFENDANT3', ADDRESS, null)) DEFENDANT3_ADDRESS,
max(decode (prefix, 'DEFENDANT4', ADDRESS, null)) DEFENDANT4_ADDRESS,
max(decode (prefix, 'DEFENDANT5', ADDRESS, null)) DEFENDANT5_ADDRESS,
max(decode (prefix, 'DEFENDANT6', ADDRESS, null)) DEFENDANT6_ADDRESS,
max(decode (prefix, 'DEFENDANT7', ADDRESS, null)) DEFENDANT7_ADDRESS,
max(decode (prefix, 'DEFENDANT8', ADDRESS, null)) DEFENDANT8_ADDRESS,
max(decode (prefix, 'DEFENDANT9', ADDRESS, null)) DEFENDANT9_ADDRESS,
---
max(decode (prefix, 'EMPLOYER1', ADDRESS, null)) EMPLOYER1_ADDRESS, 
max(decode (prefix, 'EMPLOYER2', ADDRESS, null)) EMPLOYER2_ADDRESS,
max(decode (prefix, 'EMPLOYER3', ADDRESS, null)) EMPLOYER3_ADDRESS,
max(decode (prefix, 'EMPLOYER4', ADDRESS, null)) EMPLOYER4_ADDRESS,
max(decode (prefix, 'EMPLOYER5', ADDRESS, null)) EMPLOYER5_ADDRESS,
max(decode (prefix, 'EMPLOYER6', ADDRESS, null)) EMPLOYER6_ADDRESS,
max(decode (prefix, 'EMPLOYER7', ADDRESS, null)) EMPLOYER7_ADDRESS,
max(decode (prefix, 'EMPLOYER8', ADDRESS, null)) EMPLOYER8_ADDRESS,
max(decode (prefix, 'EMPLOYER9', ADDRESS, null)) EMPLOYER9_ADDRESS,
---
max(decode (prefix, 'GUSRDIAN1', ADDRESS, null)) GUSRDIAN1_ADDRESS, 
max(decode (prefix, 'GUSRDIAN2', ADDRESS, null)) GUSRDIAN2_ADDRESS,
max(decode (prefix, 'GUSRDIAN3', ADDRESS, null)) GUSRDIAN3_ADDRESS,
max(decode (prefix, 'GUSRDIAN4', ADDRESS, null)) GUSRDIAN4_ADDRESS,
max(decode (prefix, 'GUSRDIAN5', ADDRESS, null)) GUSRDIAN5_ADDRESS,
max(decode (prefix, 'GUSRDIAN6', ADDRESS, null)) GUSRDIAN6_ADDRESS,
max(decode (prefix, 'GUSRDIAN7', ADDRESS, null)) GUSRDIAN7_ADDRESS,
max(decode (prefix, 'GUSRDIAN8', ADDRESS, null)) GUSRDIAN8_ADDRESS,
max(decode (prefix, 'GUSRDIAN9', ADDRESS, null)) GUSRDIAN9_ADDRESS,
---
max(decode (prefix, 'INSURER1', ADDRESS, null)) INSURER1_ADDRESS, 
max(decode (prefix, 'INSURER2', ADDRESS, null)) INSURER2_ADDRESS,
max(decode (prefix, 'INSURER3', ADDRESS, null)) INSURER3_ADDRESS,
max(decode (prefix, 'INSURER4', ADDRESS, null)) INSURER4_ADDRESS,
max(decode (prefix, 'INSURER5', ADDRESS, null)) INSURER5_ADDRESS,
max(decode (prefix, 'INSURER6', ADDRESS, null)) INSURER6_ADDRESS,
max(decode (prefix, 'INSURER7', ADDRESS, null)) INSURER7_ADDRESS,
max(decode (prefix, 'INSURER8', ADDRESS, null)) INSURER8_ADDRESS,
max(decode (prefix, 'INSURER9', ADDRESS, null)) INSURER9_ADDRESS,
---
---
max(decode (prefix, 'BARRISTER1', SUBURB, null)) BARRISTER1_SUBURB, 
max(decode (prefix, 'BARRISTER2', SUBURB, null)) BARRISTER2_SUBURB,
max(decode (prefix, 'BARRISTER3', SUBURB, null)) BARRISTER3_SUBURB,
max(decode (prefix, 'BARRISTER4', SUBURB, null)) BARRISTER4_SUBURB,
max(decode (prefix, 'BARRISTER5', SUBURB, null)) BARRISTER5_SUBURB,
max(decode (prefix, 'BARRISTER6', SUBURB, null)) BARRISTER6_SUBURB,
max(decode (prefix, 'BARRISTER7', SUBURB, null)) BARRISTER7_SUBURB,
max(decode (prefix, 'BARRISTER8', SUBURB, null)) BARRISTER8_SUBURB,
max(decode (prefix, 'BARRISTER9', SUBURB, null)) BARRISTER9_SUBURB,
---
max(decode (prefix, 'DOCTOR1', SUBURB, null)) DOCTOR1_SUBURB, 
max(decode (prefix, 'DOCTOR2', SUBURB, null)) DOCTOR2_SUBURB,
max(decode (prefix, 'DOCTOR3', SUBURB, null)) DOCTOR3_SUBURB,
max(decode (prefix, 'DOCTOR4', SUBURB, null)) DOCTOR4_SUBURB,
max(decode (prefix, 'DOCTOR5', SUBURB, null)) DOCTOR5_SUBURB,
max(decode (prefix, 'DOCTOR6', SUBURB, null)) DOCTOR6_SUBURB,
max(decode (prefix, 'DOCTOR7', SUBURB, null)) DOCTOR7_SUBURB,
max(decode (prefix, 'DOCTOR8', SUBURB, null)) DOCTOR8_SUBURB,
max(decode (prefix, 'DOCTOR9', SUBURB, null)) DOCTOR9_SUBURB,
---
max(decode (prefix, 'DEFENDANT1', SUBURB, null)) DEFENDANT1_SUBURB, 
max(decode (prefix, 'DEFENDANT2', SUBURB, null)) DEFENDANT2_SUBURB,
max(decode (prefix, 'DEFENDANT3', SUBURB, null)) DEFENDANT3_SUBURB,
max(decode (prefix, 'DEFENDANT4', SUBURB, null)) DEFENDANT4_SUBURB,
max(decode (prefix, 'DEFENDANT5', SUBURB, null)) DEFENDANT5_SUBURB,
max(decode (prefix, 'DEFENDANT6', SUBURB, null)) DEFENDANT6_SUBURB,
max(decode (prefix, 'DEFENDANT7', SUBURB, null)) DEFENDANT7_SUBURB,
max(decode (prefix, 'DEFENDANT8', SUBURB, null)) DEFENDANT8_SUBURB,
max(decode (prefix, 'DEFENDANT9', SUBURB, null)) DEFENDANT9_SUBURB,
---
max(decode (prefix, 'EMPLOYER1', SUBURB, null)) EMPLOYER1_SUBURB, 
max(decode (prefix, 'EMPLOYER2', SUBURB, null)) EMPLOYER2_SUBURB,
max(decode (prefix, 'EMPLOYER3', SUBURB, null)) EMPLOYER3_SUBURB,
max(decode (prefix, 'EMPLOYER4', SUBURB, null)) EMPLOYER4_SUBURB,
max(decode (prefix, 'EMPLOYER5', SUBURB, null)) EMPLOYER5_SUBURB,
max(decode (prefix, 'EMPLOYER6', SUBURB, null)) EMPLOYER6_SUBURB,
max(decode (prefix, 'EMPLOYER7', SUBURB, null)) EMPLOYER7_SUBURB,
max(decode (prefix, 'EMPLOYER8', SUBURB, null)) EMPLOYER8_SUBURB,
max(decode (prefix, 'EMPLOYER9', SUBURB, null)) EMPLOYER9_SUBURB,
---
max(decode (prefix, 'GUSRDIAN1', SUBURB, null)) GUSRDIAN1_SUBURB, 
max(decode (prefix, 'GUSRDIAN2', SUBURB, null)) GUSRDIAN2_SUBURB,
max(decode (prefix, 'GUSRDIAN3', SUBURB, null)) GUSRDIAN3_SUBURB,
max(decode (prefix, 'GUSRDIAN4', SUBURB, null)) GUSRDIAN4_SUBURB,
max(decode (prefix, 'GUSRDIAN5', SUBURB, null)) GUSRDIAN5_SUBURB,
max(decode (prefix, 'GUSRDIAN6', SUBURB, null)) GUSRDIAN6_SUBURB,
max(decode (prefix, 'GUSRDIAN7', SUBURB, null)) GUSRDIAN7_SUBURB,
max(decode (prefix, 'GUSRDIAN8', SUBURB, null)) GUSRDIAN8_SUBURB,
max(decode (prefix, 'GUSRDIAN9', SUBURB, null)) GUSRDIAN9_SUBURB,
---
max(decode (prefix, 'INSURER1', SUBURB, null)) INSURER1_SUBURB, 
max(decode (prefix, 'INSURER2', SUBURB, null)) INSURER2_SUBURB,
max(decode (prefix, 'INSURER3', SUBURB, null)) INSURER3_SUBURB,
max(decode (prefix, 'INSURER4', SUBURB, null)) INSURER4_SUBURB,
max(decode (prefix, 'INSURER5', SUBURB, null)) INSURER5_SUBURB,
max(decode (prefix, 'INSURER6', SUBURB, null)) INSURER6_SUBURB,
max(decode (prefix, 'INSURER7', SUBURB, null)) INSURER7_SUBURB,
max(decode (prefix, 'INSURER8', SUBURB, null)) INSURER8_SUBURB,
max(decode (prefix, 'INSURER9', SUBURB, null)) INSURER9_SUBURB
---
from 
(select mp.nmatter as nmatter, mp.category as category, mp.prefix as prefix, p.* from matterparty mp, phonebook p 
where 
mp.nname=p.nname 
and mp.nmatter=125 
---and mp.CATEGORY='Barrister' 
order by mp.nmatter, mp.prefix) a 
group by nmatter

Open in new window

OracleCrosstabQuery.xls
0
Comment
Question by:Sinclair_David
  • 5
  • 5
10 Comments
 
LVL 2

Expert Comment

by:gnovakhs2n
ID: 24077141
well, oracle 11 has a pivot() - function

in oracle 10, there is not really convenient way todto what you want to do here, so the decode-approach you are using is a valid solution in my opinion.

you can build the above query using dynamic sql (e.g. creating a view in a stored procedute)  to contain all the PTYPES in the table

see also http://www.experts-exchange.com/Database/Oracle/10.x/Q_23447260.html
0
 

Author Comment

by:Sinclair_David
ID: 24077229
Thanks for the prompt reply on this.  Really appreciated.

Oracle 11 is not an option at this stage.  Could you plase assist to build the query using dynamic sql.

Many thanks

Regards
0
 
LVL 2

Expert Comment

by:gnovakhs2n
ID: 24077546
OK ... the columns you want are
search, name, address, suburb
right?

Use this select in the code snippet in a cursor to build the DECOCDE - Lines dynamically. The rest should be ease: create a view in a stored procedure using EXECUTE IMMEDIATE:

begin
...
<build select using the attached snippet>
...
execute immediate('CREATE or replace VIEW my_PIVOT as  ' || your_dynamic_select)
end;
select 'max(decode (prefix, ''' || ptype || ''', ' || col || ', null)) ' || ptype || '_' || col from
(select distinct mp.prefix as ptype from matterparty mp) ptypes
,(
select 'search' as col from dual 
UNION
select 'name' from dual
UNION
select 'address' from dual
UNION
select 'suburb' from dual) columns

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Sinclair_David
ID: 24083118
Many thanks.  I can see where you are going with this.  I should have stated that I am a novice at SQL and creating procedures etc, so I need a little more help please.

Can you provide the full SQL of the above to create the stored procedure, or am asking for too much.

Many thanks
0
 
LVL 2

Expert Comment

by:gnovakhs2n
ID: 24084315
ok, try the attaced snippet ... as i have no oracle db here at the moment to test it, there might be some minor errors

CREATE OR REPLACE Procedure crosstabView
IS
    vstr varchar2(10000);
    decstr varchar2(1000);
 
    cursor c1 is
    select 'max(decode (prefix, ''' || ptype || ''', ' || col || ', null)) ' || ptype || '_' || col from
(select distinct mp.prefix as ptype from matterparty mp) ptypes
,(
select 'search' as col from dual 
UNION
select 'name' from dual
UNION
select 'address' from dual
UNION
select 'suburb' from dual) columns 
;
 
BEGIN
 
vstr:='CREATE or replace VIEW my_PIVOT as select nmatter ';
 
open c1;
fetch c1 into decstr;
 
vstr:=vstr || ',' ||  decstr
 
 
 
close c1;
 
vstr:=vstr || ' from  (select mp.nmatter as nmatter, mp.category as category, mp.prefix as prefix, p.* from matterparty mp, phonebook p where mp.nname=p.nname and mp.nmatter=125 order by mp.nmatter, mp.prefix) a group by nmatter';
 
execute immediate (vstr);
 
 
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Open in new window

0
 

Author Comment

by:Sinclair_David
ID: 24084614
That's great thank you...almost there now I think!!

However the view which is created "my_PIVOT" only contains the single DECODE statement see below:=


CREATE OR REPLACE VIEW AXIOM.MY_PIVOT
(NMATTER, APPLICANT1_ADDRESS)
AS
select nmatter ,max(decode (prefix, 'APPLICANT1', address, null)) APPLICANT1_address from  (select mp.nmatter as nmatter, mp.category as category, mp.prefix as prefix, p.* from matterparty mp, phonebook p where mp.nname=p.nname and mp.nmatter=20 order by mp.nmatter, mp.prefix) a group by nmatter;

The part of the procedure which creates the columns being

select 'max(decode (prefix, ''' || ptype || ''', ' || col || ', null)) ' || ptype || '_' || col from
(select distinct mp.prefix as ptype from matterparty mp where mp.nmatter=20) ptypes
,(
select 'search' as col from dual
UNION
select 'name' from dual
UNION
select 'address' from dual
UNION
select 'suburb' from dual) columns

produces

max(decode (prefix, 'SOLICITOR1', address, null)) SOLICITOR1_address
max(decode (prefix, 'SOLICITOR1', name, null)) SOLICITOR1_name
max(decode (prefix, 'SOLICITOR1', search, null)) SOLICITOR1_search
max(decode (prefix, 'SOLICITOR1', suburb, null)) SOLICITOR1_suburb
max(decode (prefix, 'INSURER1', address, null)) INSURER1_address
max(decode (prefix, 'INSURER1', name, null)) INSURER1_name
max(decode (prefix, 'INSURER1', search, null)) INSURER1_search
max(decode (prefix, 'INSURER1', suburb, null)) INSURER1_suburb

but this is not added to the build of the view select statement.  Does the statement need a loop anywhere??

Also, I note that the variable  vstr varchar2(10000) could potentially exceed the max string size for varchar2, is there an alternative or am I missing something.

Many thanks for your swift reply.
0
 
LVL 2

Expert Comment

by:gnovakhs2n
ID: 24085069
oh, yes i forgot the loop, sorry. varchar2(10000) should work in a procedure.

...
vstr:='CREATE or replace VIEW my_PIVOT as select nmatter ';
 
FOR decstr in c1
LOOP
vstr:=vstr || ',' ||  decstr
END LOOP;

vstr:=vstr || ' from  (select mp.nmatter as nmatter, mp.category as category, mp.prefix as prefix, p.* from matterparty mp, phonebook p where mp.nname=p.nname and mp.nmatter=125 order by mp.nmatter, mp.prefix) a group by nmatter';
 
execute immediate (vstr);
....
0
 

Author Comment

by:Sinclair_David
ID: 24085225

Again many thanks for that, however the procedure will not compile now.  it complains with an error saying
PROCEDURE CROSSTABVIEW
On line:  26
PLS-00103: Encountered the symbol "END" when expecting one of the following:

it is failing at the END LOOP line

0
 
LVL 2

Assisted Solution

by:gnovakhs2n
gnovakhs2n earned 500 total points
ID: 24085627
that one should compile as i have an oracle system now:
create or replace
Procedure crosstabView
IS
    vstr varchar2(10000);
 
 
    
    cursor c1 is
    select 'max(decode (prefix, ''' || ptype || ''', ' || col || ', null)) ' || ptype || '_' || col as val from
(select distinct mp.prefix as ptype from matterparty mp) ptypes
,(
select 'search' as col from dual 
UNION
select 'name' from dual
UNION
select 'address' from dual
UNION
select 'suburb' from dual) columns 
;
 
 
 
 
BEGIN
 
 
vstr:='CREATE or replace VIEW my_PIVOT as select nmatter ';
 
FOR decstr in c1
LOOP
 
vstr:=vstr || ',' ||  decstr.val;
END LOOP;
 
vstr:=vstr || ' from  (select mp.nmatter as nmatter, mp.category as category, mp.prefix as prefix, p.* from matterparty mp, phonebook p where mp.nname=p.nname and mp.nmatter=125 order by mp.nmatter, mp.prefix) a group by nmatter';
 
execute immediate (vstr);
 
 
 
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Open in new window

0
 

Accepted Solution

by:
Sinclair_David earned 0 total points
ID: 24086367
Thank you for your help on this.  That really did the trick.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question