Cross Tab Queries

Often we need to use cross tab queries
we can use Pivot function of sqlserver 2005.
consider an example
say, we have following sort of table
DECLARE @Test TABLE (
SalesPart char(11),
WCDate datetime,
[Delivery Date] datetime,
Qty int
)

INSERT INTO @Test
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'21-MAR-10 00:00:00.000',113), 4147 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'22-MAR-10 00:00:00.000',113), 1963 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'23-MAR-10 00:00:00.000',113), 2639 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'24-MAR-10 00:00:00.000',113), 4498 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'25-MAR-10 00:00:00.000',113), 3523 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'27-MAR-10 00:00:00.000',113), 6721
We can write a Cross Tab query for the above data
like
SELECT SalesPart, WCDate, Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday
FROM (
SELECT SalesPart, WCDate, datename(weekday ,[Delivery Date]) AS weekday, Qty
FROM @Test
) AS T
PIVOT (SUM(Qty) FOR weekday in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Saturday])) AS Pvt
Another Solution could be
SELECT T.SalesPart,


T.WCDate,

SUM(CASE WHEN CA.week_day = 'Sunday' THEN T.Qty ELSE 0 END) AS Sunday,

SUM(CASE WHEN CA.week_day = 'Monday' THEN T.Qty ELSE 0 END) AS Monday,

SUM(CASE WHEN CA.week_day = 'Tuesday' THEN T.Qty ELSE 0 END) AS Tuesday,

SUM(CASE WHEN CA.week_day = 'Wednesday' THEN T.Qty ELSE 0 END) AS Wednesday,

SUM(CASE WHEN CA.week_day = 'Thursday' THEN T.Qty ELSE 0 END) AS Thursday,

SUM(CASE WHEN CA.week_day = 'Friday' THEN T.Qty ELSE 0 END) AS Friday,

SUM(CASE WHEN CA.week_day = 'Saturday' THEN T.Qty ELSE 0 END) AS Saturday

FROM @Test T

CROSS APPLY (SELECT DATENAME(WEEKDAY, T.[Delivery Date])) CA (week_day)

GROUP BY

T.SalesPart,

T.WCDate;