0

Please help me understand how to implement the ROLLUP on this pivot table.

I have been looking over several of the other written solutions to my requirement, but I seem to be missing something when I apply them to my situation. SQL is not my strength, but I feel like I understand more each time I accomplish something.

Creating this pivot statement below was a little struggle since I need to work with dynamic fields to summarize the total for different dID depending on the cID that the report is running.

I am using a method to dynamically create the NVARCHAR that will represent the columns I am applying SUM to in the pivot table. I've attempted to use the ROLLUP function as part of the GROUP BY condition of the SELECT without success.

The general layout I would like is something like this below. Additionally, I have a second pivot with the same dID from a different dataset that I would like to merge the Totals from both into a new table, and create a GrandTotal. I imagine this would be best accomplished with a UNION

  • dID, dName, qID's, dID_Total
  • 1, A, 100-113, #
  • 2, B, 100-113, #
  • 3, C, 100-113, #
  • 4, D, 100-113, #

  • 5, E, 100-113, #

    DECLARE @sID int = 100
DECLARE @cID int = 5        
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(qID)
FROM(SELECT qID FROM qTable WHERE cID = @cID) AS QuestionID

DECLARE@ PivotTableSQL NVARCHAR(MAX)
SET@ PivotTableSQL = N'
  SELECT 
    dID, dName, ' + @ColumnName + '
  FROM (
    SELECT dID, dName, qID, weighted AS     [score]
    FROM sourceTable
    WHERE sID = ' + CAST(@sID AS nvarchar(8)) + ' AND cID = ' + CAST(@cID AS nvarchar(8)) + '
  ) AS PivotData
  PIVOT (
    SUM(score)
    FOR [qID] IN (
      ' + @ColumnName + '
    )
  ) AS PivotTable
  GROUP BY
    dID, dName,' + @ColumnName
EXECUTE(@PivotTableSQL)
Byron Ferguson
  • 21
  • 5
  • Please use the `N'prefix'` on all Unicode strings, consider [using `sp_executesql` for potentially better plan reuse](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp-executesql.aspx), and also [be very careful about declaring things like `nvarchar` without length](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx). – Aaron Bertrand Jan 20 '15 at 18:53
  • I did see the N'prefix' on other examples, and honestly did not understand what that was there for, but I have put it back in the example. I'll also add a length to nvarchar – Byron Ferguson Jan 20 '15 at 18:56
  • [Stackoverflow Example](http://stackoverflow.com/questions/17140559/using-pivot-table-with-column-and-row-totals-in-sql-server-2008), this is another question I have been using to try and resolve my question. – Byron Ferguson Jan 20 '15 at 19:00

0 Answers0