一、得出表字段相加值
set nocount on
select [name] into #ColumnTable from syscolumns where id=object_id('索取字段的表名')
exec('alter table #ColumnTable
add QueryID int identity(1,1)')
declare @num int
select @num=count(*) from #ColumnTable--获取临时表行数
declare @numstart int
set @numstart=1--循环变量
declare @varstring varchar(100)
declare @varcharendstring nvarchar(4000)--最终字段
set @varcharendstring=''
while @numstart<=@num
begin
select @varstring=name from #columntable where queryid=@numstart
set @varcharendstring=@varcharendstring+@varstring+','
set @numstart=@numstart+1
end
select @varcharendstring
drop table #ColumnTable
二、从物理文件里读取列值到数据库表中
Create table tmp_Test (ColumnName nvarchar(1000))
bulk insert tmp_Test from N'C:\ttt.txt'
Select * from tmp_Test
Drop table tmp_Test
三、把表里物定行的值转换为列
select zljyxm_xmbh,zljyxm_xmmc into #TempTableOne from zljyxm--(测试表名)
exec ('alter table #TempTableOne add QueryID int identity(1,1)')
declare @CountNumber int
declare @CountStart int
declare @TempColumnName varchar(200)
set @countstart=1
set @TempColumnName=''
select @countnumber=count(*) from #TempTableOne
while @countstart<=@countnumber
begin
select @tempcolumnname='['+zljyxm_xmmc+']' from #TempTableOne where queryid=@countstart
exec ('alter table #TempTableOne add '+@tempcolumnname+' float')
set @countstart=@countstart+1
end
go
exec ('alter table #temptableone drop column zljyxm_xmbh,zljyxm_xmmc')
Truncate Table #temptableone
select * from #temptableone
drop table #temptableone
评论