假设我有一张表：
region CustomerName MonthlyTotal recordDate
Jiangsu Restaurant 1220.00 2014-01-14
Jiangsu Restaurant 150.00 2014-01-19
Shanghai Hotel 2000.00 2012-10-11
Shanghai Hotel 200.00 2012-10-12
Shanghai Hotel 200.00 2012-11-12
我想查询出来的结果变成：
region CustomerName 2014年一月 2012-10年十月 2012-10年十一月
Jiangsu Restaurant 1370.00 null null
Shanghai Hotel null 2200.00 200.00
我了解到pivot有透视功能。但我现在遇到的难题是何将recordDate里的一个月里的每一天时间记录最后转换成
某年某月并显示
------解决方案--------------------
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'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78
(2 行受影响)
*/