PIVOT with MONTH ()

Question

How can I rotate a column with dates as a month?

Example:

Data:

|-----------------------------------| | def_kstnr | def_zeit | def_datum | |-----------------------------------| | 100 | 3.2 | 2011-11-02 | | 110 | 2.8 | 2011-02-03 | | 120 | 5.4 | 2011-11-04 | | 130 | 2.4 | 2011-08-05 | | 140 | 4.9 | 2011-09-06 | | 150 | 1.5 | 2011-10-07 | | 160 | 2.6 | 2011-12-08 | |-----------------------------------| 

Query:

 SELECT def_kstnr, [1] AS Jan, [2] AS Feb, [3] AS Mrz, [4] AS Apr, [5] AS Mai, [6] AS Jun, [7] AS Jul, [8] AS Aug, [9] AS Sep, [10] AS Okt, [11] AS Nov, [12] AS Dez FROM dbo.def PIVOT ( SUM(def_zeit) FOR MONTH(def_datum) IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) ) AS pvtMonth GROUP BY dbo.def.def_kstnr, MONTH(def_datum) 

I get this error:

Incorrect syntax near '('.

(Line with "FOR MONTH (def_datum)")

The result should look like this:

 |-----------------------------------------------------------------------------------| | def_kstnr | Jan | Feb | Mrz | Apr | Mai | Jun | Jul | Aug | Sep | Okt | Nov | Dez | |-----------------------------------------------------------------------------------| | 100 | | | | | | | | | | | 3.2 | | | 110 | | 2.8 | | | | | | | | | | | ... |-----------------------------------------------------------------------------------| 

Thanks:)

Answer 1

If you move the month function to a preliminary source, PIVOT will work. Notice, I don’t think you need to group things following the fulcrum.

 SELECT def_kstnr, [1] AS Jan, [2] AS Feb, [3] AS Mrz, [4] AS Apr, [5] AS Mai, [6] AS Jun, [7] AS Jul, [8] AS Aug, [9] AS Sep, [10] AS Okt, [11] AS Nov, [12] AS Dez FROM (Select def_kstnr, def_zeit, MONTH(def_datum) as TMonth from dbo.def) source PIVOT ( SUM(def_zeit) FOR TMonth IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) ) AS pvtMonth