sql-serverpivotsql-server-2014dayofmonth

SQL Server 2014 pivot table row and column month grant total


How can I add row and column grand totals in the table below? I want to get total and grand total by months.

However, I couldn't do that.

    SELECT *
FROM(
  SELECT
    YEAR(DueDate) [Year],
    CASE MONTH(DueDate)
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 THEN 'December'
    END as [Month],
    ProductID,
    OrderQty
  FROM Production.WorkOrder
) WorkOrders
PIVOT
(
  SUM(OrderQty)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) AS PivotTable
ORDER BY [Year], ProductID

Solution

  • 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)),
        ()
    );