01.create table temp
02.(
03. date datetime,
04. category varchar(3),
05. amount money
06.)
07.
08.insert into temp values ('1/1/2012', 'ABC', 1000.00)
09.insert into temp values ('2/1/2012', 'DEF', 500.00)
10.insert into temp values ('2/1/2012', 'GHI', 800.00)
11.insert into temp values ('2/10/2012', 'DEF', 700.00)
12.insert into temp values ('3/1/2012', 'ABC', 1100.00)
13.
14.
15.DECLARE @cols AS NVARCHAR(MAX),
16. @query AS NVARCHAR(MAX);
17.
18.SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category)
19. FROM temp c
20. FOR XML PATH(''), TYPE
21. ).value('.', 'NVARCHAR(MAX)')
22. ,1,1,'')
23.
24.set @query = 'SELECT date, ' + @cols + ' from
25. (
26. select date
27. , amount
28. , category
29. from temp
30. ) x
31. pivot
32. (
33. max(amount)
34. for category in (' + @cols + ')
35. ) p '
36.
37.
38.execute(@query)
39.
40.drop table temp
Load balance : Server 00
