select EquipmentID,[concentration],[instantFlow],[outputQuantity],[pressure],[temperature],ColTime
from (select a.EquipmentID,
[FieldName],
FieldValue,
ColTime
from KS_EquipRec201703 a
left join KS_DAttribute b
on a.DPrcArrID=b.AttrID) pvt pivot (max(FieldValue) for
FieldName in ([concentration],[instantFlow],[outputQuantity],[pressure],[temperature])) a
select * from (
select em.RamCode,em.EquipMentName,us.ID as UserID,us.Name as UserName,cla.ClassID,cla.CategoryName as ClassName,sit.SiteID,sit.SiteName,
--round(dr.StaRecCumAmt,0) as StaRecCumAmt,
--round(dr.StaRecInsCumAmt,0) as StaRecInsCumAmt,
--round(dr.WorRecCumAmt,0) as WorRecCumAmt,
--ROUND(dr.WorRecInsCumAmt,0) as WorRecInsCumAmt,
--round(dr.RecPressure,0) as RecPressure,
--round(dr.RecTemperature,0) as RecTemperature,
--round(dr.RecConcentration,0) as RecConcentration,
usn.CategoryName as NatureName,pud.ProvinceName+cud.CityName+diu.DistrictName as DistrictName,
--dr.ColTime,
emm.ManagerID,
a.EquipmentID,[concentration],[instantFlow],[outputQuantity],[pressure],[temperature],ColTime
from (select a.EquipmentID,
[FieldName],
FieldValue,
ColTime
from KS_EquipRec201703 a
left join KS_DAttribute b
on a.DPrcArrID=b.AttrID) pvt pivot (max(FieldValue) for
FieldName in ([concentration],[instantFlow],[outputQuantity],[pressure],[temperature])) a --历史数据
left join KS_EquipMent em on em.EquipMentID=a.EquipMentID --设备
left join KS_Site sit on sit.SiteID=em.SiteID --站点
left join KS_AreaUser us on us.ID=sit.UserID --用户
left join KS_Product pro on pro.ProductID=em.ProductID --产品
left join KS_Class cla on cla.ClassID=pro.ClassID --类别
left join KS_UserNature usn on usn.UserID=em.UserNatureID --性质
left join KS_Districts_Used diu on diu.DistrictID=us.DistrictID --区
left join KS_Citys_Used cud on cud.CityID=diu.CityID --市
left join KS_Provinces_Used pud on pud.ProvinceID=cud.ProvinceID
left join KS_EquipMent_Manager emm on emm.EquipMentID = em.EquipMentID
left join KS_Manager mr on emm.ManagerID=mr.ManagerID
left join KS_Role ro on mr.RoleID=ro.RoleID )a where (1=1)
from (select a.EquipmentID,
[FieldName],
FieldValue,
ColTime
from KS_EquipRec201703 a
left join KS_DAttribute b
on a.DPrcArrID=b.AttrID) pvt pivot (max(FieldValue) for
FieldName in ([concentration],[instantFlow],[outputQuantity],[pressure],[temperature])) a
select * from (
select em.RamCode,em.EquipMentName,us.ID as UserID,us.Name as UserName,cla.ClassID,cla.CategoryName as ClassName,sit.SiteID,sit.SiteName,
--round(dr.StaRecCumAmt,0) as StaRecCumAmt,
--round(dr.StaRecInsCumAmt,0) as StaRecInsCumAmt,
--round(dr.WorRecCumAmt,0) as WorRecCumAmt,
--ROUND(dr.WorRecInsCumAmt,0) as WorRecInsCumAmt,
--round(dr.RecPressure,0) as RecPressure,
--round(dr.RecTemperature,0) as RecTemperature,
--round(dr.RecConcentration,0) as RecConcentration,
usn.CategoryName as NatureName,pud.ProvinceName+cud.CityName+diu.DistrictName as DistrictName,
--dr.ColTime,
emm.ManagerID,
a.EquipmentID,[concentration],[instantFlow],[outputQuantity],[pressure],[temperature],ColTime
from (select a.EquipmentID,
[FieldName],
FieldValue,
ColTime
from KS_EquipRec201703 a
left join KS_DAttribute b
on a.DPrcArrID=b.AttrID) pvt pivot (max(FieldValue) for
FieldName in ([concentration],[instantFlow],[outputQuantity],[pressure],[temperature])) a --历史数据
left join KS_EquipMent em on em.EquipMentID=a.EquipMentID --设备
left join KS_Site sit on sit.SiteID=em.SiteID --站点
left join KS_AreaUser us on us.ID=sit.UserID --用户
left join KS_Product pro on pro.ProductID=em.ProductID --产品
left join KS_Class cla on cla.ClassID=pro.ClassID --类别
left join KS_UserNature usn on usn.UserID=em.UserNatureID --性质
left join KS_Districts_Used diu on diu.DistrictID=us.DistrictID --区
left join KS_Citys_Used cud on cud.CityID=diu.CityID --市
left join KS_Provinces_Used pud on pud.ProvinceID=cud.ProvinceID
left join KS_EquipMent_Manager emm on emm.EquipMentID = em.EquipMentID
left join KS_Manager mr on emm.ManagerID=mr.ManagerID
left join KS_Role ro on mr.RoleID=ro.RoleID )a where (1=1)