PIVOTE con MES ()

¿Cómo puedo pivotar para una columna con dates como mes?

Ejemplo:

Datos:

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

Consulta:

 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) 

Me sale este error:

Sintaxis incorrecta cerca '('.

(Alinee con "FOR MONTH (def_datum)")

El resultado debería verse así:

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

Gracias 🙂

Si mueve la function Mes a una tabla fuente anterior, PIVOT funciona. Tenga en count que no creo que deba ir a agrupar cosas siguiendo el pivote.

 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