Tuesday, January 4, 2011

Pivot with Dynamic Columns

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

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

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


DECLARE   @dtFrom DATETIME
        , @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,'')
FROM (
    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('')
)o(list)

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
OUTPUT

1 comments:

Post a Comment