Search This Blog

Dynamic PIVOT

Most of the times we want to create a result in which the columns are dynamic (for example, they are read from a table), then we need to construct the PIVOT query dynamically.  Here is an example which uses the dynamic PIVOT functionality.

CREATE TABLE dbo.sales(
      SalesPerson VARCHAR(100) ,
      STATE CHAR(2) ,
      Amount INT
) 
INSERT INTO dbo.sales
VALUES ( 'John', 'MD', 120 ),
        ( 'John', 'VA', 500 ),
        ( 'John', 'NY', 900 ),
        ( 'Jane', 'MD', 40 ),
        ( 'Jane', 'VA', 125 )
DECLARE @cols AS NVARCHAR(MAX) ,
       @query AS NVARCHAR(MAX); 
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.State)
                   FROM dbo.sales c
                   FOR XML PATH(''),TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
SET @query = 'SELECT SalesPerson, ' + @cols + ' from  
            ( 
                SELECT SalesPerson 
                    , STATE 
                    , AMOUNT 
                FROM dbo.sales 
           ) x 
            PIVOT  
            ( 
                 MAX(amount) 
                FOR state IN ('
   + @cols + ') 
            ) p '
EXECUTE(@query) 
DROP TABLE dbo.sales

No comments: