22,210
社区成员
发帖
与我相关
我的任务
分享
select timeString,[0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]
from
(select startDate as timeString,avg(DATEPART(hh,startDT)) as timeField,round(sum(powerExpend),1) as param
from building.MacRoom60KPI as kpi,common.DimMacRoom as room,common.DimTime as dimTime
where startDate between '2012-03-01' and '2012-03-07'
and kpi.timeid = dimTime.id and kpi.macroomid = room.id
and city='aa' and buildingName='bb'
and room.name='cc'
group by startDate,DATEPART(hh,startDT))
AS SourceTable
pivot(avg(param)
for timeField in([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]))
AS PivotTable order by timeString