SQL SERVER: Cross tab Query with Pivot (Dynamic Pivot)

As I mentioned earlier, PIVOT, in my earlier post, where we have reviewed the PIVOT with static columns. In practical world, we may need to have dynamic columns that needs to be displayed as below:

 

As Mentioned in Image, Here, dates are dynamic, based on Order Date, 10/25,10/26,10/27,10/28 etc..To achieve the same with Dynamic columns please find query for the same:

 

CREATE TABLE #TestTable(ctrl_no INT, id int, Amount NUMERIC(18,2), OrderDate DATE)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

DECLARE @MinDate DATE
	,@MaxDate DATE
DECLARE @Dates VARCHAR(MAX)

SELECT @Dates = ''

SELECT @MinDate = MIN(OrderDate)
	,@MaxDate = MAX(OrderDate)
FROM #TestTable

WHILE @MinDate <= @MaxDate
BEGIN
	SELECT @Dates = @Dates + '[' + CONVERT(VARCHAR(10), @MinDate, 101) + '],'

	SELECT @MinDate = DATEADD(d, 1, @MinDate)
END

SELECT @Dates = LEFT(@Dates, LEN(@Dates) - 1)

DECLARE @qry NVARCHAR(MAX)

SELECT @qry = N'

SELECT    id,
        ' + @Dates + '
FROM (
    SELECT    id,
            ISNULL(Amount,0) AS Amount,
            OrderDate
    FROM #TestTable t
) x
PIVOT(
    SUM(Amount) FOR OrderDate IN(' + @Dates + ')
) p '

EXEC (@qry)

DROP TABLE #TestTable

Let me know if you have any question/comment.

Reference : Tejas Shah http://www.SQLYoga.com