Pivot Table with T-SQL Dynamically– Another Sample

CREATE TABLE [dbo].[Test1](
[id] [int] IDENTITY(1,1) NOT NULL,
[PersonName] [varchar](50) NULL,
[PaymentDate] [datetime] NULL,
[Paymentamount] [int] NULL

VALUES (‘A’,’1/1/2012′,100),

DECLARE @cols NVARCHAR(4000), @sql NVARCHAR(4000)

SELECT @cols = COALESCE(@cols + ‘,’, ” )+ ‘[‘+shortname+’]’ FROM (
SELECT PaymentDate, STUFF(CONVERT(VARCHAR(15), PaymentDate, 107), 4, 7, ‘-‘) AS shortName FROM test1 ) t GROUP BY shortname Order by MIN(PaymentDate)

–PRINT @cols

SET @sql = ‘SELECT PersonName ,’ +@cols + ‘ FROM ( SELECT PersonName,Paymentamount, STUFF(CONVERT(VARCHAR, [PaymentDate], 107), 4, 7, ”-”) AS shortname FROM Test1) src
PIVOT( SUM(Paymentamount) FOR shortname IN (‘ + @cols + ‘)) pvt ORDER BY PersonName ‘

EXEC sp_executesql @sql


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s