SQL SERVER 一组数据按规律横着放置，少则补空，如人员按一进一出的规律，进出为一组，缺少的补null

假设一组数据：人员进出刷卡数据表【SwingCard】

ID	MenID	Door
1	1	In
2	1	In
3	1	Out
4	1	In
5	1	Out
6	1	Out

 

 

 

 

 

 

 

 

 

想要变成如下：一进一出为一组横着排放，缺少数据的补null

MenID	Door1	Door2
1	In	null
1	In	Out
1	In	Out
1	null	Out

 

 

 

 

 

 

 

实现：

--重新排序，序号为浮点型，装入临时表#Copy
select MenID,Door,Row=(Row_Number() over(order by ID)+0.0) into #Copy from SwingCard
--插入一行空数据到临时表#Copy头部
insert into #Copy(MenID,Door,Row) values （1，null , 0.0）
--相邻之间错位连接
with tempLink as (select a.MenID,a.Door Door1,b.Door Door2,a.Row from #Copy a left join #Copy b on a.Row=b.Row-1),
--将相邻之间Door相同的查出来（Door相同，表示该处缺少数据）
tempLoss as (select MenId,Null Door,Row+0.5 Row from tempLink where Door1=Door2)
--将缺少的数据补到#Copy
insert into #Copy(MenID,Door,Row) select * from tempLoss；
--对插入数据的临时表#Copy重新排序（此时临时表已经补全数据）
with tempNew as (select top 100000 MenID,Door,Row=Row_Number() Over(order by Row) from #Copy)，
--相邻之间再次错位连接
with tempNewLink as (select a.MenID,a.Door Door1,b.Door Door2 from tempNew  a left join tempNew  b on a.Row=b.Row-1)
--获取正确数据
select * from tempNewLink  where Door1='In' or Door2='Out'
--删除临时表
drop table #Copy