Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 25, 2016 - 5:48 pm UTC

Last updated: August 27, 2016 - 2:12 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

First, the following sql return this.
select
trim(p.proc_name) as com_client
,op.name as trait_name
, op.value trait_value
from
oen_procinfo p
, oen_personality op
where
op.interfaceid = p.interfaceid
and not p.proc_name like 'OEN%'
and trim(op.name) in ('ENABLEESCAPES', 'ENABLEESIESCAPES' )
and trim(p.proc_name) in ('RT_ADT_OUT', 'RT_ORM_OUT_LIS')
;
COM_CLIENT TRAIT_NAME TRAIT_VALUE
RT_ADT_OUT ENABLEESCAPES 1
RT_ADT_OUT ENABLEESIESCAPES 0
RT_ORM_OUT_LIS ENABLEESCAPES 1
RT_ORM_OUT_LIS ENABLEESIESCAPES 0

Second, after pivoting the above, I got this below.
select * from
(
select
trim(p.proc_name) as com_client
,op.name as trait_name
, op.value trait_value
from
oen_procinfo p
, oen_personality op
where
op.interfaceid = p.interfaceid
and not p.proc_name like 'OEN%'
and trim(op.name) in ('ENABLEESCAPES', 'ENABLEESIESCAPES' )
and trim(p.proc_name) in ('RT_ADT_OUT', 'RT_ORM_OUT_LIS')
) R
pivot (
max(trait_value) for (trait_name) in ('ENABLEESCAPES', 'ENABLEESIESCAPES' )
)
order by 1
;
COM_CLIENT ENABLEESCAPES ENABLEESIESCAPES
RT_ADT_OUT 1 0
RT_ORM_OUT_LIS 1 0

Question: how could I pivot the last result so that I want this output below.
TRAIT_NAME RT_ADT_OUT RT_ORM_OUT_LIS
ENABLEESCAPES 1 1
ENABLEESIESCAPES 0 0

P.S. I want to do this because I have 40+ com_clients and 500+ Trait_names, and I want the later to become the rows.
Thanks



and Chris said...

I'm not sure why you need to "pivot twice". Just change your original pivot to be on com_client:

CREATE TABLE t
    (COM_CLIENT varchar2(14), TRAIT_NAME varchar2(16), TRAIT_VALUE int)
;
    
INSERT INTO t VALUES ('RT_ADT_OUT', 'ENABLEESCAPES', 1);
INSERT INTO t VALUES ('RT_ADT_OUT', 'ENABLEESIESCAPES', 0);
INSERT INTO t VALUES ('RT_ORM_OUT_LIS', 'ENABLEESCAPES', 1);
INSERT INTO t VALUES ('RT_ORM_OUT_LIS', 'ENABLEESIESCAPES', 0);

select * from t
pivot (
  max(trait_value) for (com_client) in ('RT_ADT_OUT', 'RT_ORM_OUT_LIS' )
);

TRAIT_NAME        'RT_ADT_OUT'  'RT_ORM_OUT_LIS'  
ENABLEESCAPES     1             1                 
ENABLEESIESCAPES  0             0

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 26, 2016 - 4:26 pm UTC

Ah ... why I never thought of that, thanks!

BTW, another question on the IN_CLAUSE.
max(trait_value) for (com_client) in (IN_CLAUSE)

Apparently I have to set the in_clause with the com_client
name one by one, as in here.
max(trait_value) for (com_client) in ('RT_ADT_OUT', ...)

If I put an inline query in the IN_CLAUSE, I got error.
ORA-00936: missing expression

i.e. IN_CLAUSE being the following.
select distinct
trim(p.proc_name) as com_client
from
oen_procinfo p
, oen_personality op
where
op.interfaceid = p.interfaceid
and not p.proc_name like 'OEN%'
order by
trim(p.proc_name)

Why can't the IN_CLAUSE be an inline query?

Connor McDonald
August 27, 2016 - 2:12 am UTC

We dont support *dynamically* generating the pivot column list.

You *can* get close by returning the data as XML.

An example here

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1