Crosstab query di SQL 2005

dulu untuk bikin crosstab Pivot tabel pakai cara berikut:

SELECT Bulan,
SUM(CASE Sales WHEN 'Agus' THEN Jumlah ELSE 0 END) AS Agus,
SUM(CASE Sales WHEN 'Dani' THEN Jumlah ELSE 0 END) AS Dani,
SUM(CASE Sales WHEN 'Fahmi' THEN Jumlah ELSE 0 END) AS Fahmi,
SUM(CASE Sales WHEN 'Firstman' THEN Jumlah ELSE 0 END) AS Firstman,
SUM(CASE Sales WHEN 'Rully' THEN Jumlah ELSE 0 END) AS Rully,
SUM(Jumlah) as [Y Total]
FROM tblSamplePivot
GROUP BY Bulan
UNION
SELECT 'X Total' AS Bulan,
SUM(CASE Sales WHEN 'Agus' THEN Jumlah ELSE 0 END) AS Agus,
SUM(CASE Sales WHEN 'Dani' THEN Jumlah ELSE 0 END) AS Dani,
SUM(CASE Sales WHEN 'Fahmi' THEN Jumlah ELSE 0 END) AS Fahmi,
SUM(CASE Sales WHEN 'Firstman' THEN Jumlah ELSE 0 END) AS Firstman,
SUM(CASE Sales WHEN 'Rully' THEN Jumlah ELSE 0 END) AS Rully,
SUM(Jumlah) as [Y Total]
FROM tblSamplePivot

sekarang di SQL 2005 pake cara berikut bisa:

SELECT Bulan,
ISNULL([Agus],0) AS Agus,
ISNULL([Dani], 0) AS Dani,
ISNULL([Fahmi], 0) AS Fahmi,
ISNULL([Firstman], 0) AS Firstman,
ISNULL([Rully], 0) AS Rully,
[X Total]
FROM(
SELECT Bulan, Sales, ISNULL(Jumlah, 0) AS Jumlah FROM tblSamplePivot
UNION
--Retrieve Data untuk Total dari tiap bulan
SELECT Bulan, 'X Total' as Sales, SUM(Jumlah) AS Jumlah FROM tblSamplePivot GROUP BY Bulan
UNION
--Retrieve Data untuk Total dari tiap Sales
SELECT 'Y Total' AS Bulan, Sales, SUM(Jumlah) AS Jumlah FROM tblSamplePivot GROUP BY Sales
UNION
--Retrieve Data untuk Total Keseluruhan
SELECT 'Y Total' AS Bulan, 'X Total' AS Sales, SUM(Jumlah) AS Jumlah FROM tblSamplePivot
) AS PS
PIVOT
(
SUM(Jumlah) FOR Sales IN(Agus, Dani, Fahmi, Firstman, Rully, [X Total])
) P

diambil dari : http://waroengvb.net/crosstab-query-menggunakan-pivot-pada-sql-server/