Blog Detail

SQL SERVER Cross tab Query with Pivot (Dynamic Pivot)
12 Dec 2012
Tejas Shah
/Project/ImageURL/SQLYoga Dynamic PIVOT with SQL SERVER_thumb[11].png
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:
 

SQLYoga Dynamic PIVOT with SQL SERVER

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.

Tags

SQLYoga Tejas Shah SQL Server Cross Tab Query PIVOT

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post