Pivot with Month()

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

SQL pivot and group by month

There were several items wrong in your query. Columns need aliases and your max() within the PIVOT was referencing an invalid column name

Select *
  From (
        Select [Month] = datename(month,day)
             , [Year]  = year(day)
             , UnitId
             , production = sum(pv_production) 
         From  MeterReading
         Group By datename(month,day)
                 ,year(day)
                 ,UnitId 
       )  Src
 Pivot ( max(production) for [Month] in ([January]
                                        ,[February]
                                        ,[March]
                                        ,[April]
                                        ,[May]
                                        ,[June]
                                        ,[July]
                                        ,[August]
                                        ,[September]
                                        ,[October]
                                        ,[November]
                                        ,[December] ) ) Pvt

SQL Pivot with Month and Year

If you want to use it as view :

SELECT * FROM   
(
    SELECT 
        Concat([Year],'-', [Month]) as [Date],
        ItemFamilyCode,
        TotalShipped
    FROM Shipping -- Or any Table Name
) t 
PIVOT(
    Sum(TotalShipped) 
    FOR [Date] IN (
        [2018-9], 
        [2018-10], 
        [2018-11], 
        [2018-12], 
        [2019-1], 
        [2019-2] -- You have to type all months until today
        )
) AS pivot_table;

And, dynamic sql if you can use it in stored procedure :

Make a table with the content of date list to generate date list string

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the category names
SELECT 
    @columns+=QUOTENAME(Date) + ','
FROM 
    DateList
ORDER BY 
    DateList;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        Concat([Year],'-', [Month]) as [Date],
        ItemFamilyCode,
        TotalShipped 
    FROM Shipping -- Or any Table Name
) t 
PIVOT(
    Sum(TotalShipped)  
    FOR [Date] IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Source : sqlservertutorial

SQL Server 2014 pivot table row and column month grant total

Stuff like this is far easier using a conditional aggregate over the restrictive PIVOT operator.

Without sample data, nor expected results, this isn't tested, but you should be able to achieve what you're after with something like this:

SELECT CASE WHEN GROUPING(DATEPART(YEAR,DueDate)) = 0 THEN
         CAST(DATEPART(YEAR,DueDate) AS varchar(50))
       ELSE 'GrandTotal' END AS [Year],
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 1 THEN OrderQty END) AS January,  --Don't use single quotes for alaises,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 2 THEN OrderQty END) AS Feburary, --it can be very confusing to read.
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 3 THEN OrderQty END) AS March,    --Single quotes are for literal strings.
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 4 THEN OrderQty END) AS April,    --Using ' for alias only work in the SELECT too,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 5 THEN OrderQty END) AS May,      --something like ORDER BY 'January' would not
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 6 THEN OrderQty END) AS June,     --order by data by the column aliases as 'January'.
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 7 THEN OrderQty END) AS July,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 8 THEN OrderQty END) AS August,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 9 THEN OrderQty END) AS September,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 10 THEN OrderQty END) AS October,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 11 THEN OrderQty END) AS November,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 12 THEN OrderQty END) AS December,
       SUM(OrderQty) AS GrandTotal,
       ProductID
FROM Production.WorkOrder
GROUP BY GROUPING SETS(
    (DATEPART(YEAR, DueDate), ProductID),
    (DATEPART(YEAR, DueDate)),
    ()
);

Improve VBA which filters pivot table field with current month + months to the year end

I assume you want to hide past months and show current plus future

Sub FilterMonth()

    Dim mthname, m As Integer, ws As Worksheet
    mNum = 6 'Format(Date, "m")
    mName = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                      "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
                        
    Set ws = ThisWorkbook.Sheets("SCC")
    With ws.PivotTables("SCC").PivotFields("Period")
        For i = 1 To 12
            .PivotItems(mName(i)).Visible = CBool(i >= mNum)
        Next
    End With
    
End Sub

---

Related Topics