Creating dynamic pivot reports on SQL Server
In one of my current projects I have some pivot reports that show count of issues in different states. Grouping depends on report. States are dynamic – admins can define new states and save them to states table. This means that I cannot hardcode names of states to queries. Instead I need dynamic pivots. In this posting I will show you how to write reporting procedures that offer dynamic pivoting capabilities.
The trick here is to create dynamic list of all possible states and use this list in dynamically generated pivot query. Here is fragment of my database, names of tables should be self-describing.
Here’s the query:
CREATE PROCEDURE XSP_REPORT_REQUESTS_BY_CATEGORY
AS
BEGIN
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(
@PivotColumnHeaders + ',[' + cast(STATUS_NAME as varchar) + ']',
'[' + cast(STATUS_NAME as varchar)+ ']'
)
FROM DOC_STATUSES
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT
*
FROM
(SELECT
dc.CATEGORY_NAME AS [Category],
ds.STATUS_NAME As name,
req.ID as ID
FROM
REQUESTS_FOR_PROPOSAL req
INNER JOIN DOC_STATUSES ds ON
req.STATUS_ID = ds.ID
INNER JOIN DOC_CATEGORIES dc ON
req.CATEGORY_ID = dc.ID
) AS Source
PIVOT(
COUNT(ID)
FOR name IN ('+@PivotColumnHeaders+')
) AS PIVOTTBL'
EXECUTE(@PivotTableSQL)
END
Output of this reporting procedure is table like this:
I don’t like this dynamic SQL in stored procedure because it can be hard to debug. But this far this solution works fine for me and I can always use views to get more complex querying logic out from reporting procedures.