SQL Server PIVOT Usage Example

Recently learned the new features of SQL 2005, probably a lot of people know, I am relatively slow, after two days are 2010 years, before I began to learn 2005. To sum up:

The table is as follows:

CREATE TABLE TB (ID varchar (m), CName varchar (), Amount money,actiondate datetime)

INSERT into TB values (' 001 ', ' A ', 2000.00, ' 2008-02-28 ')

INSERT into TB values (' 001 ', ' A ', 1560.00, ' 2008-03-28 ')

INSERT into TB values (' 001 ', ' A ', 2040.00, ' 2008-01-28 ')

INSERT into TB values (' 001 ', ' A ', 1003.00, ' 2008-04-28 ')

INSERT into TB values (' ' ', ' B ', 3100.00, ' 2008-03-28 ')

INSERT into TB values (' ' ', ' B ', 4040.00, ' 2008-01-28 ')

INSERT into TB values (' ' ', ' B ', 5003.00, ' 2008-04-28 ')

INSERT into TB values (' ' ', ' B ', 2400.00, ' 2008-02-28 ')

INSERT into TB values (' 001 ', ' A ', 15000.00, ' 2007-12-21 ')

INSERT into TB values (' ' ', ' B ', 22400.00, ' 2007-12-21 ')

---------to convert the data into the following:

ID CName 2007-12 2008-01 2008-02 2008-03 2008-04

001 a 15000.00 2040.00 2000.00 1560.00 1003.00

b 22400.00 4040.00 2400.00 3100.00 5003.00

The implementation steps are as follows:

-----

Select Id,cname,sum (amount) as amount,left (CONVERT (varchar, actiondate,120), 7) as Actiondate

Into #tmp

From TB

Group BY Id,cname,left (CONVERT (varchar, actiondate,120), 7)

DECLARE @sql varchar (max), @sqlAll nvarchar (max)

Select @sql = '

Select @sql = @sql + ' [' +actiondate+ '], '

From #tmp GROUP by actiondate

Select @sql =left (@sql, Len (@sql)-1)

Print @sql

Select @sqlAll = '

SELECT * FROM #tmp s

Pivot

(Sum (amount)

For actiondate in (' + @sql + ')

As P order by Id,cname '

Print @sqlAll

------STEP4

EXEC sp_executesql @sqlAll

drop table #tmp