Incorrect syntax near 'for'. Excepted '(' or '.' — while using Pivot table in sql server

    SELECT *
FROM (
       select  PeopleSoft.[Common Name],Role.Role
   FROM eng_kpi.tbl_Projectx_EC_Action as EC_Action
   Left outer join eng_kpi.tbl_ProjectX_EC_Assignments  as EC_Assignments on EC_Assignments.EC_Number=EC_Action.EC_ID and EC_Assignments.Role_ID between 160 and 165
   left outer join eng_kpi.tbl_ProjectX_Role as Role on Role.Role_ID = EC_Assignments.Role_ID
   Left outer join eng_kpi.PeopleSoft as PeopleSoft on [Emp Num]= EC_Assignments.[User_ID]
   where   EC_ID=174391 and  Workflow_ID=257 and Date_Completed is null and Date_Cancelled is null and Date_Started is not null 
) as DataTable    
 PIVOT
(    
[Common Name] for [role] in ('Austin OPS Level 1','Austin OPS Level 2','Austin OPS Level 3','Austin Finance Level 1','Austin Finance Level 2','Austin Finance Level 3')  
)

Select query result :

选择查询结果:

  Common Name                 Role
    AJ Jones                  Austin OPS Level 2
    Matthew Lousma            Austin OPS Level 3
    Neil Harris               Austin Finance Level 2
    Neil Harris               Austin Finance Level 3

Am getting Syntax error at FOR Keyword. May i know where i am doing mistake.

我在FOR Keyword上收到语法错误。我可以知道我在哪里做错了。

1 个解决方案

#1

From the documentation:

从文档:

PIVOT ( <aggregation function>(<column being aggregated>) FOR

PIVOT( <聚合函数> ( <正在聚合的列> )FOR

You need to apply an aggregation function on [Common Name], something like MIN([Common Name]).

您需要在[Common Name]上应用聚合函数,例如MIN([Common Name])。

There are other errors too from what I can see. The IN clause items should be wrapped in square brackets and you need to alias the PIVOT:

我所看到的还有其他错误。 IN子句项应该用方括号括起来,你需要别名PIVOT:

( MIN([Common Name]) FOR [role] IN ( ['Austin OPS Level 1'],
                                     ['Austin OPS Level 2'] ) ) AS Foo

So complete example would be something like:

如此完整的例子将是这样的:

SELECT  *
FROM    ( SELECT    PeopleSoft.[Common Name] ,
                    Role.Role
          FROM      eng_kpi.tbl_Projectx_EC_Action AS EC_Action
          LEFT OUTER JOIN eng_kpi.tbl_ProjectX_EC_Assignments AS EC_Assignments 
                     ON EC_Assignments.EC_Number = EC_Action.EC_ID 
                        AND EC_Assignments.Role_ID BETWEEN 160 AND 165
          LEFT OUTER JOIN eng_kpi.tbl_ProjectX_Role AS Role 
                     ON Role.Role_ID = EC_Assignments.Role_ID
          LEFT OUTER JOIN eng_kpi.PeopleSoft AS PeopleSoft 
                     ON [Emp Num] = EC_Assignments.[User_ID]
          WHERE     EC_ID = 174391
                    AND Workflow_ID = 257
                    AND Date_Completed IS NULL
                    AND Date_Cancelled IS NULL
                    AND Date_Started IS NOT NULL
        ) AS DataTable PIVOT
( MIN([Common Name]) FOR [role] IN ( ['Austin OPS Level 1'],
                                     ['Austin OPS Level 2']... ) )  AS Foo

#1