CODEWITHSUNDEEP
sql-server
Pat
Pat

Reputation: 51

SQL error message - Incorrect syntax near ')'

I get this error message

"Incorrect syntax near ')'."

from the sum(PayHours) line and I would appreciate a bit of guidance on how to go about solving it. I am stuck.

[#PremierThree] from code below is a temporary table being created on the fly and drop afterwards

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)

Upvotes: 0

Views: 542

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

A pivot query does not contain 2 or more pivot clauses in T-SQL see FROM - Using PIVOT and UNPIVOT

You are attempting to output Hours and Dollars as "2 column sets" and frankly I find the T-SQL implementation of pivot too limiting for that. You could try an approach of using unpivot and pivot together to get the effect you want (example here).

Personally I would go for a more traditional style of pivot query that simply uses GROUP BY and case expressions as necessary (the style we always had to use before "pivot was added to SQL at all). e.g. (but I leave the column aliases up to you)

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!

If the above approach looks too old hat and wordy for you. Try combining the 2 pivots using a join, below I have assumed it would need a full outer join but it might work with a simpler inner join if both subqueries produce the same rows (which is quite likely in your case using that temp table).

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

For T-SQL: Personally, even with a join approach I don't see any advantage to using pivot when it starts to get complex.

Upvotes: 1

Related Questions