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