Generate Dynamic Pivot SQL Query

, ('2013-9-01', CAST(200.00 AS Decimal(18, 2)))

, ('2013-10-01', CAST(320.00 AS Decimal(18, 2)))

, ('2013-11-01', CAST(140.00 AS Decimal(18, 2)))

, ('2013-11-01', CAST(220.00 AS Decimal(18, 2)))

, ('2014-7-01', CAST(200.00 AS Decimal(18, 2)))

, ('2014-8-01', CAST(65.00 AS Decimal(18, 2)))

, ('2014-9-01', CAST(95.00 AS Decimal(18, 2)))

, ('2014-10-01', CAST(200.00 AS Decimal(18, 2)))

, ('2013-11-01', CAST(350.00 AS Decimal(18, 2)))

, ('2013-12-01', CAST(200.00 AS Decimal(18, 2)))

, ('2013-12-01', CAST(550.00 AS Decimal(18, 2)))

, ('2014-11-01', CAST(385.00 AS Decimal(18, 2)))

, ('2014-11-01', CAST(200.00 AS Decimal(18, 2)))

, ('2014-11-01', CAST(45.00 AS Decimal(18, 2)))

, ('2014-12-01', CAST(75.00 AS Decimal(18, 2)))

, ('2014-12-01', CAST(265.00 AS Decimal(18, 2)))

Go

SELECT year(TranDate) as Year, left(datename(month,TranDate),3) as Month, TranAmount as Amount FROM tblTransaction

The data should be displayed as in the following format:



You need to write a SQL Script using Pivot but you can generate a SQL Script by executing the following Script for the "tblTransaction" table created above.

USE [tempdb]

GO

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(month)

FROM (SELECT distinct month(TranDate) as m, left(datename(month,TranDate),3)as [month] FROM dbo.tblTransaction AS p

) AS x;

SET @sql = N' SELECT * FROM

(

SELECT

year(TranDate) as [year],left(datename(month,TranDate),3)as [month],

TranAmount as Amount

FROM tblTransaction

) AS j

PIVOT

(

SUM(Amount) FOR [month] IN ('

+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

+ ')

) AS p;';

Print @sql;

When the preceding script is executed you should get the Pivot SQL Script in your SQL Server Management Studio Editor as shown below. Copy and paste then execute the generated script.



Now enjoy what you have the very easy way!!!! :)

A point of interest: you can modify the script depending on your database records.

Finally

USE [tempdb]

GO

Drop Table tblTransaction