ECC iProcurement, iProcurement Catalog, SQL2

Description
Columns: Ecc Spec Id, Us Shopping Category, Us Shopping Category 1, Us Shopping Category 2, Us Shopping Category 3, Us Shopping Category 4, Us Shopping Category 5 ...
Imported from ECC
SELECT * FROM(
              SELECT
              ecc_spec_id,
              shopping_category,
              CASE
              WHEN shopping_category_5 IS NOT NULL THEN shopping_category_5
              WHEN shopping_category_4 IS NOT NULL THEN shopping_category_4
              WHEN shopping_category_3 IS NOT NULL THEN shopping_category_3
              WHEN shopping_category_2 IS NOT NULL THEN shopping_category_2
              ELSE 'Others' END AS shopping_category_1 ,
              CASE
              WHEN shopping_category_5 IS NOT NULL AND shopping_category_4 IS NOT NULL  THEN shopping_category_4
              WHEN shopping_category_4 IS NOT NULL AND shopping_category_3 IS NOT NULL  THEN shopping_category_3
              WHEN shopping_category_3 IS NOT NULL AND shopping_category_2 IS NOT NULL  THEN shopping_category_2
              WHEN  shopping_category_2 IS NOT NULL AND shopping_category_1 IS NOT NULL  THEN shopping_category_1
              ELSE shopping_category_1
              END AS shopping_category_2,
              CASE
              WHEN shopping_category_5 IS NOT NULL AND shopping_category_4 IS NOT NULL  AND shopping_category_3 IS NOT NULL  THEN shopping_category_3
              WHEN shopping_category_4 IS NOT NULL AND shopping_category_3 IS NOT NULL  AND shopping_category_2 IS NOT NULL  THEN shopping_category_2
              WHEN shopping_category_3 IS NOT NULL AND shopping_category_2 IS NOT NULL  AND shopping_category_1 IS NOT NULL  THEN shopping_category_1
              ELSE null
              END AS shopping_category_3,
              CASE
	      WHEN shopping_category_5 IS NOT NULL AND shopping_category_4 IS NOT NULL  AND shopping_category_3 IS NOT NULL AND shopping_category_2 IS NOT NULL  THEN shopping_category_2
	      WHEN shopping_category_4 IS NOT NULL AND shopping_category_3 IS NOT NULL  AND shopping_category_2 IS NOT NULL AND shopping_category_1 IS NOT NULL  THEN shopping_category_1
	      ELSE null
              END AS shopping_category_4,
              CASE
	      WHEN shopping_category_5 IS NOT NULL AND shopping_category_4 IS NOT NULL  AND shopping_category_3 IS NOT NULL AND shopping_category_2 IS NOT NULL AND shopping_category_1 IS NOT NULL  THEN shopping_category_1
	      ELSE null
              END AS shopping_category_5,
              LANGUAGE
              FROM  icx_ecc_category_hierarchy WHERE LANGUAGE in ( 'US')  ) PIVOT(
                MAX(SHOPPING_CATEGORY) AS SHOPPING_CATEGORY,
                MAX(SHOPPING_CATEGORY_1) AS SHOPPING_CATEGORY_1,
                MAX(SHOPPING_CATEGORY_2) AS SHOPPING_CATEGORY_2,
                MAX(SHOPPING_CATEGORY_3) AS SHOPPING_CATEGORY_3,
                MAX(SHOPPING_CATEGORY_4) AS SHOPPING_CATEGORY_4,
                MAX(SHOPPING_CATEGORY_5) AS SHOPPING_CATEGORY_5
                FOR LANGUAGE IN ('US' "US"))