How to use pivot operator to get the following output-CodePudding

Image of the Question

I need to get the above output from the given table. I've tried this and only getting values for the 1st column. Can someone check my code and give me a feedback on this regard.

SELECT * FROM (
SELECT category_name,status,order_id
FROM orders ) t 
PIVOT(
COUNT(order_id)
FOR category_name
IN (
'CPU',
'Video Card',
'Mother Board',
'Storage'
)
)
order by status

My output

CodePudding user response：

Your query, although ugly written, returns result you expected:

Sample data (modified category names, didn't feel like typing that much; because of that, I also modified values in the query):

SQL> with orders (category_name, status, order_id) as
  2    (select 'cpu'    , 'cancelled', 67 from dual union all
  3     select 'video'  , 'pending'  , 68 from dual union all
  4     select 'cpu'    , 'cancelled', 69 from dual union all
  5     select 'cpu'    , 'shipped'  , 74 from dual union all
  6     select 'mother' , 'shipped'  , 75 from dual union all
  7     select 'video'  , 'shipped'  , 76 from dual union all
  8     select 'video'  , 'pending'  , 78 from dual union all
  9     select 'storage', 'shipped'  , 82 from dual union all
 10     select 'storage', 'shipped'  , 89 from dual union all
 11     select 'storage', 'pending'  , 91 from dual
 12    )

Your query:

 13  SELECT * FROM (
 14  SELECT category_name,status,order_id
 15  FROM orders ) t
 16  PIVOT(
 17  COUNT(order_id)
 18  FOR category_name
 19  IN (
 20  'cpu',           -- I modified these names
 21  'video',         -- so that they match
 22  'mother',        -- sample data
 23  'storage'
 24  )
 25  )
 26  order by status;

STATUS         'cpu'    'video'   'mother'  'storage'
--------- ---------- ---------- ---------- ----------
cancelled          2          0          0          0
pending            0          2          0          1
shipped            1          1          1          2

SQL>

As you can see, values are here.

It means that either there are no rows you expect in that table, or you misspelled category names; you must exactly match category names (both names and letter case). Can't verify that as I don't have your data, so - you'll have to do it. See it by

select distinct category_name from orders;