CODEWITHSUNDEEP
sqlpivot
Tolerator
Tolerator

Reputation: 3

Why am I getting a syntax error using SQL pivot?

I'm getting a syntax error at 1 JAN.

My SampleStart is in datetime format. Am I aliasing something wrong?

SELECT * FROM (
  SELECT year(SampleStart) year, month(SampleStart) month, AllowedPopulation
  FROM BIC_AGGREGATE_FINAL
  WHERE SampleStart BETWEEN '2018-01-01' AND '2018-12-31'
) as s
PIVOT (
  max(AllowedPopulation) 
  FOR month in (
    1 JAN, 2 FEB, 3 MAR, 4 APR, 5 MAY, 6 JUN,
    7 JUL, 8 AUG, 9 SEP, 10 OCT, 11 NOV, 12 DEC
  )
)
ORDER BY year DESC

I'm expecting the results to show me the allowed population for each month in the year 2018.

Upvotes: 0

Views: 51

Answers (2)

GarethD
GarethD

Reputation: 69789

You cannot supply your column aliases within the pivot function, you have to do this in the select. Also, when specifying your IN clause within the pivot you must apply similar rules to when selecting columns, if the column is a reserved word, or contains special characters (e.g. space), or starts with a number etc then you must enclose the name in square brackets.

Finally, you have to give the pivot function itself an alias.

e.g.

SELECT  pvt.[year],
        JAN = pvt.[1],
        FEB = pvt.[2],
        MAR = pvt.[3],
        APR = pvt.[4],
        MAY = pvt.[5],
        JUN = pvt.[6],
        JUL = pvt.[7],
        AUG = pvt.[8],
        SEP = pvt.[9],
        OCT = pvt.[10],
        NOV = pvt.[11],
        DEC = pvt.[12]
FROM (
  SELECT YEAR(SampleStart) AS [year], MONTH(SampleStart) AS [month], AllowedPopulation
  FROM BIC_AGGREGATE_FINAL
  WHERE SampleStart BETWEEN '2018-01-01' AND '2018-12-31'
) AS s
PIVOT (
  MAX(AllowedPopulation) 
  FOR month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) 
) AS pvt -- Alias here too
ORDER BY pvt.[year] DESC

If, as the question states SampleStart is a DATETIME column, then I would make two changes to your where clause:

  1. Use a exclusive date range instead of BETWEEN - What do BETWEEN and the devil have in common?
  2. Use the unambiguous date literal format yyyyMMdd, depending on your settings 2018-10-01 could either be 10th January, or 1st October.

So you would end up with:

   WHERE SampleStart >= '20180101' 
   AND SampleStart < '20190101'

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try with-

SELECT  year,
[1] AS JAN,
[2] AS FEB,
[3] AS MAR,
[4] AS APR,
[5] AS MAY,
[6] AS JUN,
[7] AS JUL,
[8] AS AUG,
[9] AS SEP,
[10] AS OCT,
[11] AS NOV,
[12] AS DEC


FROM 
(
    SELECT year(SampleStart) year, 
    month(SampleStart) month, 
    AllowedPopulation
    FROM BIC_AGGREGATE_FINAL
    WHERE SampleStart BETWEEN '2018-01-01' AND '2018-12-31'
) as s
PIVOT 
(
max(AllowedPopulation) 
FOR month in (
    [1], [2], [3], [4], [5], [6],
    [7], [8], [9], [10], [11], [12]
)
)AS PVT
ORDER BY year DESC

Upvotes: 2

Related Questions