PIVOT with MONTH()
If you move the Month function into a prior sourcetable the PIVOT works. Note I don't think you need to go grouping things following the pivot.
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
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With
--Random past 0-798 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (798+1)*RAND()),GETDATE()))
GO 1000
Demo 1: Getting Monthly Data
SELECT YEAR(SalesDate) [Year], MONTH(SalesDate) [Month],
DATENAME(MONTH,SalesDate) [Month Name], COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate), MONTH(SalesDate),
DATENAME(MONTH, SalesDate)
ORDER BY 1,2
RESULT:-
Demo 2: Getting Monthly Data using PIVOT
SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
DATENAME(MONTH, SalesDate) [Month],
COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate),
DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count])
FOR Month IN ([January],[February],[March],[April],[May],
[June],[July],[August],[September],[October],[November],
[December])) AS MNamePivot
RESULT:-