代码之家 › 专栏 › 技术社区 › Pat

SQL错误消息-靠近“)”的语法不正确

sql-server

Pat · 技术社区 · 9 年前

我收到这个错误消息

“靠近“)”的语法不正确。”

从 sum(PayHours) 如果能给我一些关于如何解决这个问题的指导,我将不胜感激。我卡住了。

[#PremierThree] 从下面的代码中可以看出,一个临时表是动态创建的,之后再删除

SELECT * 
FROM [#PremierThree]
PIVOT 
    (SUM(PayHours)
     FOR [Pay_ClassHrs] IN ([PBS_Hrs], [POT_Hrs], [PED_Hrs], [POR_Hrs],   [Agency_Hrs], [PWH_Hrs], [Sick_Hrs], [PTO_Hrs], [Holiday_Hrs], [PJB_Hrs], [Bugeted_Work_Hrs], [Bugeted_Benefit_Hrs])
    ) AS pvt1
PIVOT 
    (SUM(PayAmount)
     FOR [Pay_Class$] in ([PBS_$], [POT_$], [PED_$], [POR_$], [Agency_$], [PWH_$], [Sick_$], [PTO_$], [Holiday_$], [PJB_$], [PNH_$],[Bugeted_Work_$], [Bugeted_Benefit_$])
    ) AS pvt2
ORDER BY
    [Facility], CAST([Job_Code] AS INT)

1 回复 | 直到 9 年前

Adrian Maxwell 9 年前

透视查询在T-SQL中不包含2个或更多透视子句,请参阅 FROM - Using PIVOT and UNPIVOT

example here ).

就我个人而言,我会选择一种更传统的透视查询风格,它只使用 GROUP BY case expressions 必要时(在“pivot”被添加到SQL之前我们一直必须使用的样式)

SELECT
      [Facility]
    , [Job_Code]

    , SUM(CASE WHEN Pay_ClassHrs = 'PBS_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'POT_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'PED_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'POR_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'Agency_Hrs' THEN Pay_ClassHrs END)          AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'PWH_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'Sick_Hrs' THEN Pay_ClassHrs END)            AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'PTO_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'Holiday_Hrs' THEN Pay_ClassHrs END)         AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'PJB_Hrs' THEN Pay_ClassHrs END)             AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'Bugeted_Work_Hrs' THEN Pay_ClassHrs END)    AS x
    , SUM(CASE WHEN Pay_ClassHrs = 'Bugeted_Benefit_Hrs' THEN Pay_ClassHrs END) AS x

    , SUM(CASE WHEN Pay_Class$ = 'POT_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'PED_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'POR_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'Agency_$' THEN Pay_Class$ END)                AS x
    , SUM(CASE WHEN Pay_Class$ = 'PWH_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'Sick_$' THEN Pay_Class$ END)                  AS x
    , SUM(CASE WHEN Pay_Class$ = 'PTO_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'Holiday_$' THEN Pay_Class$ END)               AS x
    , SUM(CASE WHEN Pay_Class$ = 'PJB_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'PNH_$' THEN Pay_Class$ END)                   AS x
    , SUM(CASE WHEN Pay_Class$ = 'Bugeted_Work_$' THEN Pay_Class$ END)          AS x
    , SUM(CASE WHEN Pay_Class$ = 'Bugeted_Benefit_$' THEN Pay_Class$ END)       AS x

FROM [#PremierThree]
GROUP BY
      [Facility]
    , [Job_Code]

未经测试!

full outer join 但它可能与更简单的 inner join 如果两个子查询产生相同的行( 在你的情况下,很可能使用临时表 ).

SELECT
       COALESCE(p1.Facility,p2.Facility) Facility
     , COALESCE(p1.Job_Code,p2.Job_Code) Job_Code
     , [PBS_Hrs], [POT_Hrs], [PED_Hrs], [POR_Hrs],   [Agency_Hrs], [PWH_Hrs], [Sick_Hrs], [PTO_Hrs], [Holiday_Hrs], [PJB_Hrs], [Bugeted_Work_Hrs], [Bugeted_Benefit_Hrs]
     , [PBS_$], [POT_$], [PED_$], [POR_$], [Agency_$], [PWH_$], [Sick_$], [PTO_$], [Holiday_$], [PJB_$], [PNH_$],[Bugeted_Work_$], [Bugeted_Benefit_$]
FROM (
      SELECT
            [Facility]
          , [Job_Code]
          , [PBS_Hrs], [POT_Hrs], [PED_Hrs], [POR_Hrs],   [Agency_Hrs], [PWH_Hrs], [Sick_Hrs], [PTO_Hrs], [Holiday_Hrs], [PJB_Hrs], [Bugeted_Work_Hrs], [Bugeted_Benefit_Hrs]
      FROM [#PremierThree]
      PIVOT 
          (SUM(PayHours)
           FOR [Pay_ClassHrs] IN ([PBS_Hrs], [POT_Hrs], [PED_Hrs], [POR_Hrs],   [Agency_Hrs], [PWH_Hrs], [Sick_Hrs], [PTO_Hrs], [Holiday_Hrs], [PJB_Hrs], [Bugeted_Work_Hrs], [Bugeted_Benefit_Hrs])
          ) AS pvt1
      ) AS p1
FULL OUTER JOIN (
      SELECT
            [Facility]
          , [Job_Code]
          , [PBS_$], [POT_$], [PED_$], [POR_$], [Agency_$], [PWH_$], [Sick_$], [PTO_$], [Holiday_$], [PJB_$], [PNH_$],[Bugeted_Work_$], [Bugeted_Benefit_$]
      PIVOT 
          (SUM(PayAmount)
           FOR [Pay_Class$] in ([PBS_$], [POT_$], [PED_$], [POR_$], [Agency_$], [PWH_$], [Sick_$], [PTO_$], [Holiday_$], [PJB_$], [PNH_$],[Bugeted_Work_$], [Bugeted_Benefit_$])
          ) AS pvt2
      ) AS p2
             ON p1.Facility = p2.Facility AND p1.Job_Code = p2.Job_Code
ORDER BY
    [Facility], CAST([Job_Code] AS INT)
;

对于T-SQL:就我个人而言,即使使用连接方法,当pivot开始变得复杂时,我也看不到它有任何优势。