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.

MSSQL pivot tables

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:

MSSQL pivot query results

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.

Liked this post? Empower your friends by sharing it!

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    One thought on “Creating dynamic pivot reports on SQL Server

    • October 10, 2012 at 8:39 pm
      Permalink

      Thanks for the SP. Does clear up a few things. I am doing a similar report which has multiple dimensions similar to this one the only issue is i need to have the query in LINQ, but the SP helps as it does clear a few doubts i had. Thanks

    Leave a Reply

    Your email address will not be published. Required fields are marked *