Solved

Oracle Crosstab query sql

Posted on 2009-04-06
10
2,512 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now