行列转换

declare @BeginTime datetime,@EndTime datetime
--declare @TreeID  UNIQUEIDENTIFIER
--set @TreeID=''${TreeID}''
SET @BeginTime=''${DateBegin} 00:00:000''
SET @EndTime=''${DateEnd} 23:59:59:999''


select Name,ID,value DoneCount,value1 TCount
,CASE LEFT(ttype,2) WHEN 'JC' THEN 0 WHEN 'GW' THEN 1 WHEN 'WH' THEN 2 WHEN 'ZY' THEN 3  END  CheckType
FROM (
select *,tType=attribute,tvalue=value FROM (
select t.Name Name,t.ID,
SUM(d.JCDoneCount) JCDoneCount,SUM(d.GWDoneCount)GWDoneCount,SUM(d.WHDoneCount)WHDoneCount,SUM(d.ZYDoneCount)ZYDoneCount,
SUM(d.JCTaskCount) JCTaskCount,SUM(d.GWTaskCount)GWTaskCount,SUM(d.WHTaskCount)WHTaskCount,SUM(d.ZYTaskCount)ZYTaskCount

from dbo.Rpt_CheckMtcStatic d
INNER JOIN dbo.Tre_ConfigTree t ON d.NodeID=t.ID
--WHERE d.TaskDate>=@BeginTime AND d.TaskDate<=@EndTime
GROUP BY t.Name,t.ID
)tb
UNPIVOT  ( value FOR attribute IN(JCDoneCount, GWDoneCount,WHDoneCount,ZYDoneCount )  ) AS UPV
)tb1   
UNPIVOT  (value1 FOR attribute1 IN(JCTaskCount, GWTaskCount,WHTaskCount,ZYTaskCount)  ) AS UPV1

WHERE LEFT(ttype,2)=LEFT(attribute1,2)

 

转载于:https://www.cnblogs.com/Snailslost/p/9284272.html