如果只有简单几行就可以拼凑一下, 若行数和列数都不固定的就要用到循环if OBJECT_ID('tempdb..#t') is not null drop table #tselect'1' as cID, N'測試課' as cClass, '81201533' as cSN, N'燕XX' as cName,null as cSign into #t union select'2', N'測試課', '81206820', N'丁XX',null union select'0', N'測試課', '81209639', N'朱XX',null union select'1', N'測試課', '81213788', N'黃X ',null union select'2', N'測試課', '81214553', N'孟XX',null union select'0', N'測試課', '81215971', N'侯X ',null union select'1', N'測試課', '81215988', N'梁XX',null union select'2', N'測試課', '81216034', N'張XX',null union select'0', N'測試課', '81222073', N'閆X ',null-- select * from #t if object_id('tempdb..#t2') is not null drop table #t2select ROW_NUMBER() over (partition by cID order by cSN) cPrt, * into #t2 from #t-- select * from #t2select * from #t2 A left join #t2 B on B.cPrt = A.cPrt and B.cID = 2 left join #t2 C on C.cPrt = A.cPrt and C.cID = 0 where A.cID = 1
union
select'2', N'測試課', '81206820', N'丁XX',null
union
select'0', N'測試課', '81209639', N'朱XX',null
union
select'1', N'測試課', '81213788', N'黃X ',null
union
select'2', N'測試課', '81214553', N'孟XX',null
union
select'0', N'測試課', '81215971', N'侯X ',null
union
select'1', N'測試課', '81215988', N'梁XX',null
union
select'2', N'測試課', '81216034', N'張XX',null
union
select'0', N'測試課', '81222073', N'閆X ',null-- select * from #t if object_id('tempdb..#t2') is not null drop table #t2select ROW_NUMBER() over (partition by cID order by cSN) cPrt, * into #t2 from #t-- select * from #t2select * from #t2 A
left join #t2 B on B.cPrt = A.cPrt and B.cID = 2
left join #t2 C on C.cPrt = A.cPrt and C.cID = 0
where A.cID = 1