Home » , » Pivot with Dynamic Columns

Pivot with Dynamic Columns

Author : Sandeep Mittal on Tuesday, January 4, 2011 | 1/04/2011 11:51:00 AM

In this post, we will learn how to achieve dynamic pivoting in SQL Server.
Pivot is used to convert unique row values of a column as columns of the resultset. If we already know all unique values of the column 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 pivoting where we will dynamically create a list of values to pass for pivoting.

Let's take an example of dynamic pivot

Table and Data Script

      CustCode  VARCHAR(50)
    , InvAmt    INT
    , InvDate    DATETIME
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 'C0002', 1200, '10/20/2010' UNION ALL 
SELECT 'C0002', 1600, '11/05/2010' UNION ALL 
SELECT 'C0002', 1300, '11/17/2010'
SELECT * from tblSales

Now, suppose you want to use pivot to display Invoice Amount for each Customer code on the basis of Invoice 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) passed, then use below code for it

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

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

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

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

+ comments + 1 comments

December 6, 2011 at 10:59 AM

Excellent pieces. Keep posting such kind of information on your blog. I really impressed by your blog.

Post a Comment