SQL的错误信息 - 附近有语法错误)“
问题描述:
我收到此错误信息SQL的错误信息 - 附近有语法错误)“
”附近有语法错误)'。“
从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)
答
下降枢轴查询不包含在T-SQL 2项或多个枢纽条款看FROM - Using PIVOT and UNPIVOT
你是试图输出小时和美元作为“2列集”,并坦率地说,我发现T-SQL实现的主要限制。您可以尝试使用unpivot并一起旋转以获得所需效果(example here)。我个人会选择一种更传统的数据透视查询方式,根据需要简单地使用GROUP BY
和case expressions
(我们总是在“透视图被添加到SQL之前必须使用的样式)。eg(但是我离开列别名由你)
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]
UNTESTED!
如果上面的方法看起来太旧帽子,罗嗦你。尝试使用加入结合2个枢轴,下面我甲肝e假设它需要一个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的好处。
你的父母很明显是不平衡的。 ))为pvt1 ...为什么两个密切的parens只有一个开放paren? – pmbAustin
为我成功编译 – Artem
你不需要所有的方括号,他们只是使这很难阅读。 –