你能给unpivot的for子句传递(2)个参数吗oracle sql

13z8s7eq 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(297)

我正在尝试解开一堆列：

基本上分为3列（旋转部分）、服务区（如“csa”、“esa”）和优先级（如“p1”、“p2”）以及工作时间。我试图避免两次取消激活，并且必须添加一个cte（或者更糟的是，一个视图上的一个视图），并且通过尝试传递两个参数（两个新列）来接近它，如下所示：

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'
                 ) 
          )
);

有没有可能不使用cte和解锁两次？这是错误的方法吗？

sql oracle unpivot

来源：https://stackoverflow.com/questions/62415374/can-you-pass-2-arguments-to-the-for-clause-of-unpivot-oracle-sql

2条答案

按热度按时间

egmofgnx1#

为什么不简单 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))

你也可以做任何事 unpivot 条款：

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') ) )

D小提琴

赞(0）回复(0）举报 2021-07-26

f1tvaqid2#

FOR ("Service_Area", "Priority") --PARANTHESIS WERE NEEDED HERE
              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') --AND AFTER EACH 'AS',
                    (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')
                 ) 
````          )

赞(0）回复(0）举报 2021-07-26

我来回答

你能给unpivot的for子句传递(2)个参数吗oracle sql

2条答案

相关问题

热门标签

最新问答