How can I pivot for a column with dates as 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)")
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 | | | | | | | | | | |
...
|-----------------------------------------------------------------------------------|
Thank you :)
Navigate to a PivotTable or PivotChart in the same workbook. Add a column from the Date table to the Column Labels or Row Labels area of the Power Pivot field list. Click the down arrow next to Column Labels or Row Labels in the PivotTable. Point to Date Filters, and then select a filter from the list.
By default, a pivot table shows only data items that have data. When a pivot table is set up to show months, this means that months can "disappear" if the source data does not contain data in that month. In the example shown, a pivot table is used to count the rows by color.
To change the date format: Right-click a date in the pivot table (not the pivot chart). Click on Field Settings. Change the Number Format to the date format that you want.
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:-
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With