• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2520
  • Last Modified:

Oracle Crosstab query sql

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
Sinclair_David
Asked:
Sinclair_David
  • 5
  • 5
2 Solutions
 
gnovakhs2nCommented:
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
 
Sinclair_DavidAuthor Commented:
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
 
gnovakhs2nCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Sinclair_DavidAuthor Commented:
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
 
gnovakhs2nCommented:
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
 
Sinclair_DavidAuthor Commented:
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
 
gnovakhs2nCommented:
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
 
Sinclair_DavidAuthor Commented:

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
 
gnovakhs2nCommented:
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
 
Sinclair_DavidAuthor Commented:
Thank you for your help on this.  That really did the trick.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now