SELECT
*
FROM
( SELECT
item.Cname ,
detail.[MasterId] ,
detail.[SamplingTime] ,
CASE WHEN detail.IsTest= 1 THEN detail.[Result] +'L' ELSE detail.[Result] end MonitorData ,
detail.[WaterBelongingCode]
FROM
[dbo].[T_SurfaceDrinkingWater_BusinessInfoDetail] AS detail
LEFT JOIN [dbo].[T_Bas_WarterItem] AS item ON detail.ItemCode = item.Id
WHERE
detail.IsOverStandard != -1
AND detail.IsOverFixStandard != -1
AND item.Cname IN ( 'PH', '水温', '溶解氧', '氨氮', '高锰酸盐指数', '电导率' )
) AS t PIVOT ( max(MonitorData) FOR Cname IN ( [PH], [水温], [溶解氧], [氨氮],
[高锰酸盐指数], [电导率] ) ) AS t4;
*
FROM
( SELECT
item.Cname ,
detail.[MasterId] ,
detail.[SamplingTime] ,
CASE WHEN detail.IsTest= 1 THEN detail.[Result] +'L' ELSE detail.[Result] end MonitorData ,
detail.[WaterBelongingCode]
FROM
[dbo].[T_SurfaceDrinkingWater_BusinessInfoDetail] AS detail
LEFT JOIN [dbo].[T_Bas_WarterItem] AS item ON detail.ItemCode = item.Id
WHERE
detail.IsOverStandard != -1
AND detail.IsOverFixStandard != -1
AND item.Cname IN ( 'PH', '水温', '溶解氧', '氨氮', '高锰酸盐指数', '电导率' )
) AS t PIVOT ( max(MonitorData) FOR Cname IN ( [PH], [水温], [溶解氧], [氨氮],
[高锰酸盐指数], [电导率] ) ) AS t4;