Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PIVOT with MONTH()

Tags:

sql

pivot

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

like image 529
Ueli Avatar asked Aug 22 '11 09:08

Ueli


People also ask

How do I filter pivot by month?

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.

Why does pivot table not show month?

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.

How do I change a pivot table Date to month?

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.


2 Answers

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
like image 70
Xhalent Avatar answered Oct 20 '22 11:10

Xhalent


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

enter image description here

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

enter image description here

like image 32
Shashikant Pandit Avatar answered Oct 20 '22 12:10

Shashikant Pandit