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:)
Best Answer
it is looking very simple after referring your previous post.. please try the below query for that ...