How to transpose row to columns in Oracle as shown below using Unpivot?

Source Table

Cost Category	Cost Category Type	Q1-2020	Q2-2020	Q3-2020	Q4-2020
Employee Impacted	Period Cost	10	20	0	4000

Achieved Result

Cost Category	Cost Category Type	Quarter Year	Value
Employee Impacted	Period Cost	Q1-2020	10
Employee Impacted	Period Cost	Q2-2020	20
Employee Impacted	Period Cost	Q3-2020	0
Employee Impacted	Period Cost	Q4-2020	4000

Desired Result

Cost Category	Cost Category Type	Quarter Year	Quarter	Year	Value
Employee Impacted	Period Cost	Q1-2020	Q1	2020	10
Employee Impacted	Period Cost	Q2-2020	Q2	2020	20
Employee Impacted	Period Cost	Q3-2020	Q3	2020	0
Employee Impacted	Period Cost	Q4-2020	Q4	2020	4000

I have been able to achieve table 2 above using below query but not sure how to add quarter and year column as shown in table 3

with a (
  Cost_Category,
  Cost_Category_Type,
  Q1_2020,
  Q2_2020,
  Q3_2020,
  Q4_2020
) as (
  select
    'Employee Impacted',
    'Period Cost',
    ATTRIBUTE_34,
    ATTRIBUTE_35,
    ATTRIBUTE_36,
    ATTRIBUTE_37
  from view_form_539766
)
select *
from a
unpivot (
  value
  for Quarter_Year in (
    q1_2020 as 'Q1-2020',
    q2_2020 as 'Q2-2020',
    q3_2020 as 'Q3-2020',
    q4_2020 as 'Q4-2020',
    
  )
)

Answer

Use yet another CTE. Your current “solution” is temp CTE in my query; then it is a simple task to extract quarter and year.

SQL> WITH
  2     a (cost_category,
  3        cost_category_type,
  4        q1_2020,
  5        q2_2020,
  6        q3_2020,
  7        q4_2020)
  8     AS
  9        (SELECT 'Employee Impacted', 'Period cost', 10, 20, 0, 4000 FROM DUAL),
 10     temp
 11     AS
 12        (SELECT *
 13           FROM a
 14                UNPIVOT (VALUE
 15                        FOR quarter_year
 16                        IN (q1_2020 AS 'Q1-2020',
 17                           q2_2020 AS 'Q2-2020',
 18                           q3_2020 AS 'Q3-2020',
 19                           q4_2020 AS 'Q4-2020')))
 20  SELECT t.cost_category,
 21         t.cost_category_type,
 22         t.quarter_year,
 23         SUBSTR (t.quarter_year, 1, 2) quarter,
 24         SUBSTR (t.quarter_year, 4) year,
 25         t.VALUE
 26    FROM temp t
 27  /

COST_CATEGORY     COST_CATEGO QUARTER QUARTER  YEAR      VALUE
----------------- ----------- ------- -------- ---- ----------
Employee Impacted Period cost Q1-2020 Q1       2020         10
Employee Impacted Period cost Q2-2020 Q2       2020         20
Employee Impacted Period cost Q3-2020 Q3       2020          0
Employee Impacted Period cost Q4-2020 Q4       2020       4000

SQL>