请问一个SQL,请高手指点,谢谢..在线等...只用SQL语句不能用存储过程?
数据集如下:
序号           姓名                               发药窗口
1 '李玉堂             ' '发药01 '
2 '魏先正             ' '发药01 '
3 '王立纯             ' '发药02 '
4 '郭青青             ' '发药01 '
5 '张菁华             ' '发药02 '
6 '付开明             ' '发药02 '
改写成这种数据集:
序号             姓名                             发药窗口1                   姓名                           发药窗口2
1 '李玉堂             ' '发药01 '                     '王立纯             ' '发药02 '
2 '魏先正             ' '发药01 '                   '付开明             ' '发药02 '
3 '郭青青             ' '发药01 '               '张菁华             ' '发药02 '




------解决方案--------------------
select identity(int,1,1) as sn,姓名,发药窗口 into #t1 from table1 where 发药窗口= '发药01 ';
select identity(int,1,1) as sn,姓名,发药窗口 into #t2 from table1 where 发药窗口= '发药02 ';
select isnull(#t1.sn,#t2.sn) as 序号,#t1.姓名,#t1.发药窗口 as 发药窗口1,#t2.姓名,#t2.发药窗口 as 发药窗口2
from #t1 full join #t2
on #t1.sn=#t2.sn
------解决方案--------------------
不用加top 3
select t.序号,t.姓名,t.发药窗口,t1.序号,t1.姓名,t1.发药窗口,t2.序号,t2.姓名,t2.发药窗口
from tb t inner join tb t1 on t1.序号=t.序号+3 inner join tb t2 on t2.序号=t1.序号+3

------解决方案--------------------
但愿这位兄弟还在线，好用的话不忘了谢一句，我居然想了5分钟，谢谢你。
--step1
create table test (iSN int null,cName char(10) null, cWindow char(10) null)

insert test values(1, '李玉堂 ', '发药01 ')
insert test values(2, '魏先正 ', '发药01 ')
insert test values(3, '王立纯 ', '发药02 ')
insert test values(4, '郭青青 ', '发药01 ')
insert test values(5, '张菁华 ', '发药02 ')
insert test values(6, '付开明 ', '发药02 ')


--step2
select t1.iSN ,t1.cName ,t1.cWindow,t2.cName,t2.cWindow from
( select (select count(*) from test a where a.iSN <= b.iSN and a.cWindow = '发药01 ') as iSN,cName,cWindow from test b where b.cWindow = '发药01 ') t1
,
( select (select count(*) from test a where a.iSN <= b.iSN and a.cWindow = '发药02 ') as iSN,cName,cWindow from test b where b.cWindow = '发药02 ') t2
where t1.iSN = t2.iSN

union

select iSN,cName,cWindow, ' ', ' ' from
( select (select count(*) from test a where a.iSN <= b.iSN and a.cWindow = '发药01 ') as iSN,cName,cWindow from test b where b.cWindow = '发药01 ') t1
where iSN not in ( select t2.iSN from
( select (select count(*) fro