PIVOT / GROUP BY issue on ORACLE

Question

My first query where I got problem here : Tricky GROUP BY issue on ORACLE is now definitely resolved.

However I have a new question. I try to transform it, one more time to have now this output :

                        |           EMAIL          |              WIFI          | ...         
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        Yes             |             20           |                24          | ...                 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
         No             |              4           |                 0          | ...        
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    Unknown             |              1           |                 1          | ...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Here the data to help you to build such output. I tried to use again unpivot / pivot with the query that René gave me in the resolved issue I quote, but unfortunately I got the error that "ORA-56901: non-constant expression is not allowed for pivot|unpivot values" sighh...

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 
)

Maybe there is a simpler solution for this ? I definitely need to read a book about relational DB:)

Answer 1

it is looking very simple after referring your previous post.. please try the below query for that ...

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)
        -- only this line I have changed  ..
        for feature in ('WIFI' as Wifi, 'EMAIL' as Email, 'BLUETOOTH' as Bluetooth)
);

Answer 2

If the number of the columns of the output table is flexible, you can probably use some procedural solution; PL/SQL or Java.

In PL/SQL you can create a two dimensional collection and populate it and then print it out. Of you can create/generate a dynamic SQL query using dbms_sql package.