日期:2014-05-16 浏览次数:20591 次
WITH a1 (region,CustomerName,MonthlyTotal,recordDate) AS
(
SELECT 'Jiangsu','Restaurant',1220.00,'2014-01-14' UNION ALL
SELECT 'Jiangsu','Restaurant',150.00,'2014-01-19' UNION ALL
SELECT 'Shanghai','Hotel',2000.00,'2012-10-11' UNION ALL
SELECT 'Shanghai','Hotel',200.00,'2012-10-12' UNION ALL
SELECT 'Shanghai','Hotel',200.00,'2012-11-12'
)
,a2 AS
(
SELECT region,CustomerName,MonthlyTotal,CONVERT(char(7),recordDate,120) recordDate FROM a1
)
SELECT *
FROM a2 a
PIVOT (SUM(MonthlyTotal) FOR recordDate IN ([2014-01],[2012-10],[2012-11])) b
ORDER BY 1
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N