NULL fields using PIVOT

I request your collaboration because pivot on a table and separating the records by null, but still leave the fields at 0 with NVL

Table

product   | value
----------+-------
Shirts    | 1200
Caps      | 0
Stocks    | 0
Glasses   | 100
Shoes     | 0

Código pivot

select * from products
 PIVOT (sum(value)
   for titles in ('product', 'value')) AS pivot_product

Result:

 product  |  Shirts   |  Caps  |    Stocks   | Glasses | Shoes
 ---------+-----------+--------+-------------+---------+----------
   value  |    NULL   |  NULL  |    NULL     |   100   |   NULL
   value  |    1200   |  NULL  |    NULL     |   NULL  |   NULL

Expected result:

 product  |  Shirts   |  Caps  |    Stocks   | Glasses | Shoes
 ---------+-----------+--------+-------------+-------+----------
   valor  |    1200   |  NULL  |    NULL     |  100  |   NULL

Optional

 product  |  Shirts   |  Caps  |    Stocks   | Glasses | Shoes
 ---------+-----------+--------+-------------+-------+----------
   valor  |    1200   |    0   |      0      |  100  |    0

Answer

You need to put the column values in the pivot list:

Oracle Setup:

CREATE TABLE test_data ( product, value ) AS
SELECT 'Shirts', 1200 FROM DUAL UNION ALL
SELECT 'Caps',      0 FROM DUAL UNION ALL
SELECT 'Stocks',    0 FROM DUAL UNION ALL
SELECT 'Glasses', 100 FROM DUAL UNION ALL
SELECT 'Shoes',     0 FROM DUAL

Query:

SELECT 'value' AS product,
       p.*
FROM   test_data
PIVOT ( SUM( value ) FOR product IN (
  'Shirts'  AS Shirts,
  'Caps'    AS Caps,
  'Stocks'  AS Stocks,
  'Glasses' AS Glasses,
  'Shoes'   AS Shoes
) ) p

Output:

PRODUCT | SHIRTS | CAPS | STOCKS | GLASSES | SHOES
:------ | -----: | ---: | -----: | ------: | ----:
value   |   1200 |    0 |      0 |     100 |     0

db<>fiddle here