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
) ON [PRIMARY]

INSERT[dbo].[Test1]
VALUES (‘A’,’1/1/2012′,100),
(‘B’,’1/2/2012′,200),
(‘C’,’1/3/2012′,300),
(‘D’,’1/4/2012′,400),
(‘E’,’1/2/2012′,500),
(‘A’,’2/1/2012′,500),
(‘B’,’2/2/2012′,300),
(‘C’,’2/3/2012′,100),
(‘D’,’2/4/2012′,200),
(‘E’,’2/5/2012′,400),
(‘A’,’3/1/2012′,200),
(‘B’,’3/3/2012′,100),
(‘C’,’3/3/2012′,300),
(‘D’,’3/4/2012′,500),
(‘E’,’3/5/2012′,400)

–Code
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

Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s