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