--如果该字段是原来一个表的数据,并且是有规律的数据
--try
declare @t table (date varchar(200))
insert into @t select '1 a b c d e'
union all select '2 a b c'
union all select '3 a b c d'
union all select '4 a b c d e'
--查询
select col1=substring(date,1,1),
col2=substring(date,3,1),
col3=substring(date,5,1),
col4=substring(date,7,1),
col5=substring(date,9,1),
col6=substring(date,11,1)
from @T
--结果
col1 col2 col3 col4 col5 col6
---- ---- ---- ---- ---- ----
1 a b c d e
2 a b c
3 a b c d
4 a b c d e(所影响的行数为 4 行)
--try
declare @t table (date varchar(200))
insert into @t select '1 a b c d e'
union all select '2 a b c'
union all select '3 a b c d'
union all select '4 a b c d e'
--查询
select col1=substring(date,1,1),
col2=substring(date,3,1),
col3=substring(date,5,1),
col4=substring(date,7,1),
col5=substring(date,9,1),
col6=substring(date,11,1)
from @T
--结果
col1 col2 col3 col4 col5 col6
---- ---- ---- ---- ---- ----
1 a b c d e
2 a b c
3 a b c d
4 a b c d e(所影响的行数为 4 行)
第二步,所有列串成一段1 a b c d e,2 a b c,3 a b c d,4 a b c d e,然后每节插入新的表,因为你的每个字段可能不止一个字串,我的代码也有考虑:CREATE table #T (strT varchar(100))
insert into #T select '1 aa b c d e'
union all select '2 a bb c'
union all select '3 a b c d'
union all select '4 a b c dd e'
select * from #T
/*
strT
----------------------------------------------------------------------------------------------------
1 aa b c d e
2 a bb c
3 a b c d
4 a b c dd e(4 row(s) affected)
*/declare @len as int
select @len = max( (len(strT)-len(replace(strT,' ','')))/1) + 1 from #T
--獲取最多字段數,这里最多为6,于是建立有6个字段的表格
declare @Col as varchar(4000) --定義表格的字段
declare @j as int --ColN
set @j = 1
set @Col = ''
while @j <= @len
begin
set @Col = @Col + ',Col' + convert(varchar(10),@j) + ' varchar(10)'
set @j = @j + 1
end
set @Col = stuff(@Col,1,1,'')
exec ('drop table TT;create table TT ( ' + @Col + ')' ) --建立表格declare @str as varchar(200) --所有列串在一起
declare @strA as varchar(200)
declare @strB as varchar(200)
set @str = ''
set @strA = ''
set @strB = ''
select @str = @str + ',' +T.strT from #T T
set @str = stuff(@str,1,1,'') + ','
while len(@str) > 0
begin
set @strA = left(@str,charindex(',',@str,1) - 1 )
declare @i as int
set @i = 6 - (len(@strA)-len(replace(@strA,' ','')))/len('#') - 1
set @strB = replace(@strA,' ',''',''')
set @strB = '''' + @strB + ''''
while @i > 0
begin
set @strB = @strB + ','''''
set @i = @i - 1
end
exec('insert into TT values(' + @strB+ ')' )
set @str = substring(@str,charindex(',',@str,1) + 1,len(@str) )
endselect * from TT
/*
Col1 Col2 Col3 Col4 Col5 Col6
---------- ---------- ---------- ---------- ---------- ----------
1 aa b c d e
2 a bb c
3 a b c d
4 a b c dd e(4 row(s) affected)
*/