一个略微复杂的Sql行转列实例

表结构如上，RoomId为房间号，IsPresent为1表示该房间有人，IsPresent为0表示该房间没人。现在需要统计每个房间以1开始、以0结束的时间，如果有连续的1则取第一个1创建时间为开始时间，如果有连续的0则取最后一个0创建时间为结束时间。

第一步

先根据RoomId和CreateTime排序，使每个房间每次开房的开始时间和结束时间连在一起，由于数据表中的自增主键不一定连续，所以添加一列连续的编号用来判断是否存在连续的1或连续的0的情况。

select *,ROW_NUMBER()over(order by RoomId,CreateTime)px into #tem_px from DisorderedLiveRecord

临时表#tem_px中的信息如下：

第二步

如果有连续的1则取第一个1创建时间为开始时间，所以要删除连续的1中除第一条外的其他记录；
如果有连续的0则取最后一个0创建时间为结束时间，所以要删除连续的0中除最后一条外的其他记录；
最终数据表中只剩下每个房间每次开房唯一的开始时间和结束时间。

查找连续的1中除第一条外其他记录的Id：
a.px=b.px+1 and a.IsPresent=b.IsPresent 说明存在连续的IsPresent值相同的情况；
a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=1 说明存在连续的IsPresent值相同且值为1的情况；
因为a.px=b.px+1，所以a.px较大，因此a.Id指的是两条IsPresent值相同的记录中位置靠后那条；
b.Id指的是两条IsPresent值相同的记录中位置靠前的那条。

select a.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=1 and a.RoomId=b.RoomId

查找连续的0中除最后一条外其他记录的Id：
a.px=b.px+1 and a.IsPresent=b.IsPresent 说明存在连续的IsPresent值相同的情况；
a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=0 说明存在连续的IsPresent值相同且值为0的情况；

select b.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=0 and a.RoomId=b.RoomId

第三步

根据需求删除IsPresent值相同的多余数据。

delete from #tem_px where Id in
(select a.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=1 and a.RoomId=b.RoomId)
delete from #tem_px where Id in
(select b.Id from #tem_px a 
 inner join #tem_px b on a.px=b.px+1 and a.IsPresent=b.IsPresent and a.IsPresent=0 and a.RoomId=b.RoomId)

删除多余数据后，临时表#tem_px中的信息如下：

第四步

此时大家发现px列已经不连续了，所以我们需要再添加一列连续的编号用来实现结束时间列转行。

select *,ROW_NUMBER()over(order by RoomId,CreateTime)px2 into #tem_px2 from #tem_px

临时表#tem_px2中的信息如下：

第五步

把IsPresent=0的结束时间作为一个新列，放到对应的IsPresent=1的开始时间之后。
注意：临时表用完之后一定要删除。

select a.RoomId,a.CreateTime as StartTime,b.CreateTime as EndTime from #tem_px2 a
left join #tem_px2 b on a.px2=b.px2 - 1 and b.IsPresent=0
where a.IsPresent=1

drop table #tem_px
drop table #tem_px2

所得结果如下：

在对SqlServer数据库表进行复杂地处理时，最重要的是要知道自己的需求是什么，以及达到这个需求需要经过哪些步骤，然后按照这些步骤写对应的Sql语句，就自然而然地得到了自己想要的结果。