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
Sales Table Data

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
Dynamic Pivot

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Share this

Related Posts

Previous
Next Post »

1 comments:

comments
December 6, 2011 at 10:59 AM delete

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

Reply
avatar