如果只有ABC select sn=indentity(int,1,1),* into #t from tablename select a.column2,a.column3,a.colum4,b.colun2,b.column3,b.column4,c.column2,c.column3,c.column4 from #t a join #t b on (select count(*) from #t where column1=a.column1 and sn<=a.sn)=(select count(*) from #t where column1=b.column1 and sn<=b.sn) and a.column1='a' and b.column1='b' join #t c on (select count(*) from #t where column1=a.column1 and sn<=a.sn)=(select count(*) from #t where column1=c.column1 and sn<=c.sn) and c.column1='c'
--建立测试环境 Create table 表1 (Name Varchar(10), COL1 Int, COL2 Int, COL3 Int ) GO--插入数据 Insert 表1 Values('A', 430, 330, 410) Insert 表1 Values('A', 420, 340, 440) Insert 表1 Values('A', 410, 390, 450) Insert 表1 Values('A', 440, 350, 430) Insert 表1 Values('A', 470, 370, 430) Insert 表1 Values('B', 430, 340, 410) Insert 表1 Values('B', 420, 350, 420) Insert 表1 Values('B', 410, 330, 440) Insert 表1 Values('B', 440, 350, 460) Insert 表1 Values('B', 470, 340, 470) Insert 表1 Values('C', 430, 330, 430) Insert 表1 Values('C', 440, 360, 780) Insert 表1 Values('C', 430, 340, 760) Insert 表1 Values('C', 440, 330, 740) Insert 表1 Values('C', 450, 320, 680) GO--测试 --Select * from 表1 Select ID=IDENTITY(Int,1,1),* Into #T from 表1 --Select * from #TSelect T1.*,T2.B1,T2.B2,T2.B3,T3.C1,T3.C2,T3.C3 from (Select ID,COL1 As A1,COL2 As A2,COL3 As A3 from #T Where Name='A') T1 Inner Join (Select ID,COL1 As B1,COL2 As B2,COL3 As B3 from #T Where Name='B') T2 On T1.ID=T2.ID-5 Inner Join (Select ID,COL1 As C1,COL2 As C2,COL3 As C3 from #T Where Name='C') T3 On T1.ID=T3.ID-10--删除测试环境 Drop table 表1 Drop table #T --结果 ID A1 A2 A3 B1 B2 B3 C1 C2 C3 1 430 330 410 430 340 410 430 330 430 2 420 340 440 420 350 420 440 360 780 3 410 390 450 410 330 440 430 340 760 4 440 350 430 440 350 460 440 330 740 5 470 370 430 470 340 470 450 320 680
select sn=indentity(int,1,1),* into #t from tablename
select a.column2,a.column3,a.colum4,b.colun2,b.column3,b.column4,c.column2,c.column3,c.column4
from #t a join #t b on (select count(*) from #t where column1=a.column1 and sn<=a.sn)=(select count(*) from #t where column1=b.column1 and sn<=b.sn) and a.column1='a' and b.column1='b'
join #t c on (select count(*) from #t where column1=a.column1 and sn<=a.sn)=(select count(*) from #t where column1=c.column1 and sn<=c.sn) and c.column1='c'
--建立测试环境
Create table 表1
(Name Varchar(10),
COL1 Int,
COL2 Int,
COL3 Int
)
GO--插入数据
Insert 表1 Values('A', 430, 330, 410)
Insert 表1 Values('A', 420, 340, 440)
Insert 表1 Values('A', 410, 390, 450)
Insert 表1 Values('A', 440, 350, 430)
Insert 表1 Values('A', 470, 370, 430)
Insert 表1 Values('B', 430, 340, 410)
Insert 表1 Values('B', 420, 350, 420)
Insert 表1 Values('B', 410, 330, 440)
Insert 表1 Values('B', 440, 350, 460)
Insert 表1 Values('B', 470, 340, 470)
Insert 表1 Values('C', 430, 330, 430)
Insert 表1 Values('C', 440, 360, 780)
Insert 表1 Values('C', 430, 340, 760)
Insert 表1 Values('C', 440, 330, 740)
Insert 表1 Values('C', 450, 320, 680)
GO--测试
--Select * from 表1
Select ID=IDENTITY(Int,1,1),* Into #T from 表1
--Select * from #TSelect T1.*,T2.B1,T2.B2,T2.B3,T3.C1,T3.C2,T3.C3 from
(Select ID,COL1 As A1,COL2 As A2,COL3 As A3 from #T Where Name='A') T1
Inner Join
(Select ID,COL1 As B1,COL2 As B2,COL3 As B3 from #T Where Name='B') T2
On T1.ID=T2.ID-5
Inner Join
(Select ID,COL1 As C1,COL2 As C2,COL3 As C3 from #T Where Name='C') T3
On T1.ID=T3.ID-10--删除测试环境
Drop table 表1
Drop table #T
--结果
ID A1 A2 A3 B1 B2 B3 C1 C2 C3
1 430 330 410 430 340 410 430 330 430
2 420 340 440 420 350 420 440 360 780
3 410 390 450 410 330 440 430 340 760
4 440 350 430 440 350 460 440 330 740
5 470 370 430 470 340 470 450 320 680