Matrix/Crosstab SQL using PIVOT functionality

Sample SQL's 

Using Decode and aggregate functions.

SELECT item
     , inventory
     , product_family
  FROM ( SELECT msi.segment1 item
, MAX( DECODE(mcs.category_set_name, 'Inventory', mc.segment1))       inventory
, MAX( DECODE(mcs.category_set_name, 'Product Family', mc.segment1))   product_family
            FROM mtl_system_items_b msi
               , mtl_item_categories mic
               , mtl_categories mc
               , mtl_category_sets mcs
           WHERE msi.organization_id = mic.organization_id
             AND msi.inventory_item_id = mic.inventory_item_id
             AND msi.segment1 = :p_item
             AND msi.organization_id = 103
             AND mic.category_set_id = mcs.category_set_id
             AND mic.category_id = mc.category_id
             AND mc.structure_id = mcs.structure_id
        GROUP BY msi.segment1)
                       


SELECT *
  FROM (SELECT msi.segment1 item
             , mcs.category_set_name category_set
             , mc.segment1
          FROM mtl_system_items_b msi
             , mtl_item_categories mic
             , mtl_categories mc
             , mtl_category_sets mcs
         WHERE 1 = 1
           AND msi.inventory_item_id = mic.inventory_item_id
           AND msi.organization_id = mic.organization_id
           AND msi.segment1 = :p_item
           AND msi.organization_id = 103
           AND mic.category_set_id = mcs.category_set_id
           AND mic.category_id = mc.category_id
           AND mc.structure_id = mcs.structure_id) 
PIVOT XML (MAX( segment1) AS category  --<-- pivot_clause
FOR( category_set)   --<-- pivot_for_clause
IN (select category_set_name from mtl_category_sets)) 
--<-- pivot_in_clause
                                                   
                                                   
                                                   
SELECT *
  FROM (SELECT msi.segment1 item
             , mcs.category_set_name category_set
             , mc.segment1
          FROM mtl_system_items_b msi
             , mtl_item_categories mic
             , mtl_categories mc
             , mtl_category_sets mcs
         WHERE 1 = 1
           AND msi.inventory_item_id = mic.inventory_item_id
           AND msi.organization_id = mic.organization_id
           AND msi.segment1 = :p_item
           AND msi.organization_id = 103
           AND mic.category_set_id = mcs.category_set_id
           AND mic.category_id = mc.category_id
           AND mc.structure_id = mcs.structure_id) 
PIVOT (MAX( segment1) AS category  --<-- pivot_clause
FOR( category_set) --<-- pivot_for_clause
IN  ('Inventory', 'Series', 'Model')) --<-- pivot_in_clause