SQL Server Pivot table using Date Ranges as columns

Hi All,

My todays topic is Pivot Table in SQL Server. Here we use a Data ranges as parameter to get the data in between that dates.

But, we see the Dates as a Column Heading.

Here we use OINV (Sales Invoice table) table to see the Customer sales values according Date as columns.

DECLARE @FROMDATE DATETIME

DECLARE @TODATE DATETIME

DECLARE @query NVARCHAR(MAX)

DECLARE @cols NVARCHAR (MAX)

SET @FROMDATE = '20170401'

SET @TODATE = '20170430'   -- DATEADD(d, 30, @FROMDATE)

Step 1 : Create a #temporary table of selected dates in between.

WHILE @FROMDATE <= @TODATE

       BEGIN

              SELECT @FROMDATE as CalendarDate

              INTO #Temp

        SET @FROMDATE = DATEADD(dd, 1, @FROMDATE)

                     WHILE @FROMDATE <= @TODATE

                     BEGIN

                           INSERT INTO #Temp

                           SELECT @FROMDATE

                           SET @FROMDATE = DATEADD(dd, 1, @FROMDATE)

                     END

       END

--SELECT * FROM #Temp

Step 2 : Create a row of Date values as a String.

SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, CalendarDate, 106) + ']',

               '[' + CONVERT(NVARCHAR, CalendarDate, 106) + ']')

               FROM (SELECT * FROM #Temp) PVT   

--SELECT @cols

Step 3 : Main code of PIVOT table, and Execute this code.

       SET @query = 'SELECT *

                     FROM 

                       (SELECT DocDate,CardName,DocTotal FROM OINV

                       ) X

                     PIVOT 

                       (SUM(DocTotal) FOR DocDate IN (' + @cols + ')

                       ) Pvt'    

       EXEC SP_EXECUTESQL @query

Step 4 : Drop the #temporary table.

DROP TABLE #Temp

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

https://plus.google.com/+ArkaGupta-arkagupta/posts