行变列，查找某一时刻附近记录

SELECT ccc.realsoetime,ccc.mac, ccc.ControlTime , sum  (case ccc.soetype when 5 then 1 else 0 end )  as '零序过流跳闸' ,    
   sum  (case ccc.soetype when 128 then 1 else 0 end )  as '开关合到分' ,   sum  (case ccc.soetype when 133 then 1 else 0 end )  as '绝缘试验',     
      max  (case ccc.soetype when 128 then ccc.soetime else 0 end )  as '开关合到分时间' ,   
        max  (case ccc.soetype when 5 then ccc.soetime else 0 end )  as '零序过流跳闸时间'  ,
        (case  when  ccc.ControlTime IS NULL   then '就地' else '远程' end )  as '操作方式'    
           from (       select [Soe_Record].*,aaa.realsoetime ,RemoteControlTryRecord.ControlTime from (      
               SELECT [soetime] as realsoetime ,[Soe_Record].soetype,mac   
                FROM [Soe_Record] where  soetype=133 and  mac='015220090804'  and   
                  soetime between  '2022-08-16 00:00:00' and '2022-08-27 00:00:00'   ) as aaa left join [Soe_Record]
                   on  (DATEDIFF(SECOND,aaa.realsoetime,[Soe_Record].soetime) BETWEEN -1 AND 1) and  [Soe_Record].mac=aaa.mac 
                   and [Soe_Record].soetime between  '2022-08-16 00:00:00' and '2022-08-27 00:00:00'
                   left join  RemoteControlTryRecord on  RemoteControlTryRecord.MACID=aaa.mac 
                    and (DATEDIFF(SECOND,aaa.realsoetime,RemoteControlTryRecord.ControlTime) BETWEEN -10 AND 10)    )  as ccc GROUP BY ccc.realsoetime,ccc.mac, ccc.ControlTime