是这样的，我有一串很长的数字串，
如：12345678901234567890
12345678901234567890
如何把他们分成20列，每列只有一个数字？
就像这样：
1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0
1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0
------解决思路----------------------
--动态处理的方法
DECLARE @ColumnNum INT
SET @ColumnNum=20
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
DECLARE @COUNT VARCHAR(10)
SET @COUNT=1
WHILE @COUNT<=@ColumnNum
SELECT @SQL=@SQL+',SUBSTRING(列名,'+@COUNT+',1)['+@COUNT+']'
,@COUNT=@COUNT+1
SET @SQL='SELECT '+STUFF(@SQL,1,1,'')+'FROM 表名'
--PRINT @SQL
EXEC(@SQL)
--写名的方法
SELECT SUBSTRING(列名,1,1)[1]
,SUBSTRING(列名,2,1)[2]
,SUBSTRING(列名,3,1)[3]
,SUBSTRING(列名,4,1)[4]
,SUBSTRING(列名,5,1)[5]
,SUBSTRING(列名,6,1)[6]
,SUBSTRING(列名,7,1)[7]
,SUBSTRING(列名,8,1)[8]
,SUBSTRING(列名,9,1)[9]
,SUBSTRING(列名,10,1)[10]
,SUBSTRING(列名,11,1)[11]
,SUBSTRING(列名,12,1)[12]
,SUBSTRING(列名,13,1)[13]
,SUBSTRING(列名,14,1)[14]
,SUBSTRING(列名,15,1)[15]
,SUBSTRING(列名,16,1)[16]
,SUBSTRING(列名,17,1)[17]
,SUBSTRING(列名,18,1)[18]
,SUBSTRING(列名,19,1)[19]
,SUBSTRING(列名,20,1)[20]
FROM 表名
------解决思路----------------------
create table sx(str0 varchar(50))
insert into sx(str0)
select '12345678901234567890' union all
select '12345678901234567890'
-- 建结果表#t
create table #t
(str1 char(1),str2 char(1),str3 char(1),str4 char(1),str5 char(1),
str6 char(1),str7 char(1),str8 char(1),str9 char(1),str10 char(1),
str11 char(1),str12 char(1),str13 char(1),str14 char(1),str15 char(1),
str16 char(1),str17 char(1),str18 char(1),str19 char(1),str20 char(1))
-- 分成20列,写入结果表#t
declare @i tinyint,@str0 varchar(50),@tsql varchar(600)
declare ap scroll cursor for select str0 from sx
open ap
fetch first from ap into @str0
while(@@fetch_status<>-1)
begin
select @i=1,@tsql=null
while(@i<=20)
begin
select @tsql=isnull(@tsql+',','select ')+''''+substring(@str0,@i,1)+''' '
select @i=@i+1
end
insert into #t exec(@tsql)
fetch next from ap into @str0
end
close ap
deallocate ap
-- 结果
select * from #t
/*
str1 str2 str3 str4 str5 str6 str7 str8 str9 str10 str11 str12 str13 str14 str15 str16 str17 str18 str19 str20
---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0
1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0
(2 row(s) affected)
*/
------解决思路----------------------
use master
go
if OBJECT_ID('a') is not null drop table a
go
create table a(tr varchar(50))
go
insert into a
select '12345678901234567890' union all
select '0123' union all
select '98765432101234567890'
go
declare @i int,@j int,@sql varchar(5000),@sqt varchar(8000)
select @i=MAX(len(tr)) from a
set @j=1
set @sql=''
set @sqt=''
while @j<=@i
begin
select @sql=@sql +',[str'+ltrim(@j)+']'
set @j=@j+1
end
set @sqt='select * from
(select tr,SUBSTRING(tr,number+1,1) as nums,''str''+LTRIM(number+1) as col
from a,master..spt_values as b where LEN(tr)>b.number and b.type=''p'' ) as c
pivot (max(nums) for col in ('+substring(@sql,2,999)+')) as d'
exec(@sqt)