Pivot with Dynamic Columns in SQL Server

In this post, you will learn about dynamic pivot in SQL Server and where it is required with an example.

Pivot is basically transpose used to convert unique row values of a column as columns of the resultset.

If we already know all unique values of the column and are static then we can use static pivot where we can pass the unique values in the query, but if we are not sure of all the unique values we will require dynamic pivot where we will have to dynamically create a list of values to pass for pivot.

Let's take an example of dynamic pivot

Lets create a sales table and populate some data

CREATE TABLE tblSales (
      CustCode  VARCHAR(50)
    , InvAmt    INT
    , InvDate    DATETIME
)

INSERT INTO tblSales
SELECT 'C0001', 1500, '10/01/2010' UNION ALL
SELECT 'C0002', 2000, '10/01/2010' UNION ALL
SELECT 'C0002', 1100, '10/25/2010' UNION ALL
SELECT 'C0001', 1200, '10/20/2010' UNION ALL
SELECT 'C0001', 1600, '11/05/2010' UNION ALL
SELECT 'C0002', 1300, '11/17/2010'

SELECT * from tblSales

OUTPUT

Now, suppose we want to use pivot to display Invoice Amount for each Customer code on the basis of Period (Month/Year) and Period will be dynamically construct as column on the basis of Invoice Period i.e (Invoice from date and to date)

DECLARE   @dtFrom DATETIME
        , @dtTo DATETIME
        , @sPeriodList VARCHAR(100)
        , @sQuery NVARCHAR(500)

SET @dtFrom = '2010-10-01'
SET @dtTo = '2010-11-30'

SELECT @sPeriodList = STUFF((
    SELECT  distinct ',[' + CONVERT(VARCHAR(7), InvDate, 111) + ']' AS [data()]
    FROM    tblSales
    WHERE  InvDate BETWEEN @dtFrom AND @dtTo    
    FOR XML PATH('')
),1,1,'')

SET @sQuery='SELECT CustCode,' + @sPeriodList + ' FROM (
  SELECT CustCode, InvAmt, CONVERT(VARCHAR(7), InvDate, 111) AS Period 
  FROM   tblSales) src
  PIVOT  (SUM(InvAmt) FOR Period IN (' + @sPeriodList + ')
) pvt'
EXEC sp_executesql @sQuery

OUTPUT