表数据转换

[leecyi]

ID,FA1,FB1,FC1,FA2,FB2,FC2,.......FAN,FBN,FCN,
-----------------------------------------------
1 A1 B1 C1 A2 B2 C2 .......AN BN CN

现在想转换为:
ID,FA,FB,FC
____________
1 A1 B1 C1
1 A2 B2 C2
........
1 AN BN CN

[wwh999]

如果想能通过函数或其他完成,你的表字段得有一定的规律才行..!

[liumang9527]

mark

[dawugui]

select id,fa1 as fa , fb1 as fb , fc1 as fc
union
select id,fa2 as fa , fb2 as fb , fc2 as fc
union
......
union
select id,fan as fa , fbn as fb , fcn as fc

[leecyi]

好几百个字段呀,写不完.

[leecyi]

：wwh999(印钞机V2.0...开发中....)

三个字段一组增长

[gahade]

create table tb(id int,fa varchar(10),fb varchar(10),fc varchar(10))

create proc up_insertdata
as
begin
declare @i int
set @i=1
declare @s int
declare @sql varchar(8000)
set @sql=''
select @s=(max(colid)-1)/3 from syscolumns where id=object_id('tb')
while @i<=@s
begin
select @sql='select id,fa'+cast(@i as varchar)+',fb'+cast(@i as varchar)+',fc'+cast(@i as varchar)+' from tablename'
insert into tb(id,fa,fb,fc)
exec(@sql)
select @sql=''
set @i=@i+1
end
end

exec up_insertdata

[paoluo]

--建立測試環境
--插入數據
Create Table TEST(ID Int,FA1 Varchar(10),FB1 Varchar(10),FC1 Varchar(10),FA2 Varchar(10),FB2 Varchar(10),FC2 Varchar(10),FA3 Varchar(10),FB3 Varchar(10),FC3 Varchar(10))
Insert TEST Select 1, 'A1', 'B1', 'C1', 'A2', 'B2', 'C2', 'A3', 'B3', 'C3'
GO
--測試
Declare @S Varchar(8000),@I Int,@J Int
Select @S='',@J = 1
Select @I = Max(Stuff(Name,1,2,'')) From SysColumns Where ID = OBJECT_ID('TEST')
While @J <= @I
Begin
Select @S = @S + ' Union All Select ID,FA'+Rtrim(@J)+' As FA,FB'+Rtrim(@J)+' As FB,FC'+Rtrim(@J)+' As FC From TEST'
Select @J = @J +1
End
Select @S = Stuff(@S,1,10,'') + ' Order By ID,FA,FB,FC'
EXEC(@S)
GO
--刪除測試環境
Drop Table TEST
/*
--結果
ID FA FB FC
1 A1 B1 C1
1 A2 B2 C2
1 A3 B3 C3
*/