Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Crosstab query sql

Posted on 2009-04-06
10
Medium Priority
?
2,518 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 

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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
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…

715 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