在Oracle 11中，可以将该pivot子句与该unpivot子句一起使用：
with
count_table as (
select 1001 device_id, 4 quantity from dual union all
select 1002 device_id, 20 quantity from dual union all
select 1003 device_id, 1 quantity from dual
),
device_table as (
select 1001 id, 'Yes' wifi, 'No' email, 'No' bluetooth from dual union all
select 1002 id, 'Yes' wifi, 'Yes' email, 'No' bluetooth from dual union all
select 1003 id, 'UnkNown' wifi, 'UnkNown' email, 'Yes' bluetooth from dual
)
----------------------------------------
select * from (
select
feature,
yes_no_unkNown,
sum(quantity) quantity
from
count_table c join
device_table d on c.device_id = d.id
unpivot ( yes_no_unkNown
for feature in (wifi, email, bluetooth)
)
group by
feature,
yes_no_unkNown
)
pivot ( sum (quantity)
for yes_no_unkNown in ('Yes' as yes, 'No' as no, 'UnkNown' as unkNown)
)
;
或者，您可能希望将两个现有表连接到包含三个所需行的值的第三个表。也可能更容易阅读：
with
count_table as (
select 1001 device_id, 4 quantity from dual union all
select 1002 device_id, 20 quantity from dual union all
select 1003 device_id, 1 quantity from dual
),
device_table as (
select 1001 id, 'Yes' wifi, 'No' email, 'No' bluetooth from dual union all
select 1002 id, 'Yes' wifi, 'Yes' email, 'No' bluetooth from dual union all
select 1003 id, 'UnkNown' wifi, 'UnkNown' email, 'Yes' bluetooth from dual
)
----------------------------------------
select
f.txt,
sum(case when ( f.txt = 'wifi' and d.wifi = 'Yes' ) or
( f.txt = 'email' and d.email = 'Yes' ) or
( f.txt = 'bluetooth' and d.bluetooth = 'Yes' )
then c.quantity
else 0 end
) yes,
sum(case when ( f.txt = 'wifi' and d.wifi = 'No' ) or
( f.txt = 'email' and d.email = 'No' ) or
( f.txt = 'bluetooth' and d.bluetooth = 'No' )
then c.quantity
else 0 end
) no,
sum(case when ( f.txt = 'wifi' and d.wifi = 'UnkNown' ) or
( f.txt = 'email' and d.email = 'UnkNown' ) or
( f.txt = 'bluetooth' and d.bluetooth = 'UnkNown' )
then c.quantity
else 0 end
) unkNown
from
count_table c join
device_table d on c.device_id = d.id cross join
(
select 'wifi' txt from dual union all
select 'email' txt from dual union all
select 'bluetooth' txt from dual
) f
group by
f.txt;
602392714
清零编程群