Search code examples
sqloracle-databaseunpivot

Can you pass (2) Arguments to the FOR clause of UNPIVOT? - ORACLE SQL

I am trying to unpivot a bunch of columns:

enter image description here

into basically 3 columns (the pivoting part), Service_Area (e.g. 'CSA', 'ESA') and Priority (e.g. 'P1', 'P2') and ActWklyHrs. I am trying to avoid UNPIVOT'ing twice and having to add a CTE (or worse, a view on a view) and am approaching it by trying to pass two arguements (two new columns) like this:

 CREATE OR REPLACE FORCE VIEW "STJOF"."vP3factPriorityDistribution" ("ISO_YearWeek", "ISO_Year", "ISO_Week", "Week_Start", "Service_Area", "DistSA_Key_Hash", "Priority", "ActWklyHrs")
AS
(
SELECT
    CONCAT(TO_CHAR(to_date(WEEK_START,'MM/DD/YYYY'),'IYYY'), TO_CHAR(to_date(WEEK_START,'MM/DD/YYYY'),'IW')) AS ISO_YEARWEEK,
    TO_CHAR(to_date(WEEK_START,'MM/DD/YYYY'),'IYYY')      AS ISO_YEAR,
    TO_CHAR(to_date(WEEK_START,'MM/DD/YYYY'),'IW')        AS ISO_WEEK,
    to_date(WEEK_START, 'MM/DD/YYYY')                     AS Week_Start,
    SERVICE_AREA                                          AS Service_Area,
    ORA_HASH(SERVICE_AREA, 99999,1)                       AS DistSA_Key_Hash,
    CASE "Priority"
    WHEN 'P1' THEN 1                                
    WHEN 'P2' THEN 2
    WHEN 'P3' THEN 3 
    END                                                   AS "Priority",
    ActWklyHrs                                            AS ActWklyHrs
  FROM "STJOF"."HistPriorityDistribution" 
  UNPIVOT ( (ActWklyHrs) 
              FOR "Service_Area", "Priority"
              IN ( (CSA_P1) AS 'CSA','P1', (ESA_P1) AS 'ESA','P1', (PCA_P1) AS 'PCA','P1', (DCA_P1) AS 'DCA','P1', (SHA_P1) AS 'SHA','P1' (WSA_P1) AS 'WSA','P1' (WHA_P1) AS 'WHA','P1',
                    (CSA_P2) AS 'CSA','P2', (ESA_P2) AS 'ESA','P2', (PCA_P2) AS 'PCA','P2', (DCA_P2) AS 'DCA','P2', (SHA_P2) AS 'SHA','P2' (WSA_P2) AS 'WSA','P2' (WHA_P2) AS 'WHA','P2',
                     (CSA_P3) AS 'CSA','P3', (ESA_P3) AS 'ESA','P3', (PCA_P3) AS 'PCA','P3', (DCA_P3) AS 'DCA','P3', (SHA_P3) AS 'SHA','P3' (WSA_P3) AS 'WSA','P3' (WHA_P3) AS 'WHA','P3'
                 ) 
          )
);

Is it possible without using a CTE and UNPIVOT'ing twice? Is this the wrong approach?

Solution

  • Why not simply substr()?

    select week_start, substr(str, 1, 3) as service, substr(str, 4) as priority, hrs
  from test 
  unpivot (hrs for str in (CSA_P1, ESA_P1, CSA_P2, ESA_P2, CSA_P3, ESA_P3))

    You can also do everything in unpivot clause:

    select *
  from test 
  unpivot (hrs for (service, priority) in (CSA_P1 as ('CSA', 'P1'),
                                           ESA_P1 as ('ESA', 'P1'),
                                           CSA_P2 as ('CSA', 'P2'),
                                           ESA_P2 as ('ESA', 'P2'),
                                           CSA_P3 as ('CSA', 'P3'),
                                           ESA_P3 as ('ESA', 'P3') ) )

    dbfiddle