--动态处理的方法 DECLARE @ColumnNum INT SET @ColumnNum=20DECLARE @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 sxopen 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 endclose 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' godeclare @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 colfrom a,master..spt_values as b where LEN(tr)>b.number and b.type=''p'' ) as cpivot (max(nums) for col in ('+substring(@sql,2,999)+')) as d' exec(@sqt)
DECLARE @ColumnNum INT
SET @ColumnNum=20DECLARE @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 sxopen 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
endclose 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)
*/
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'
godeclare @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 colfrom a,master..spt_values as b where LEN(tr)>b.number and b.type=''p'' ) as cpivot (max(nums) for col in ('+substring(@sql,2,999)+')) as d' exec(@sqt)
--建表代码
create table #1123
([1] char(1),[2] char(1),[3] char(1),[4] char(1),[5] char(1),[6] char(1),[7] char(1),[8] char(1),[9] char(1),[10] char(1),
[11] char(1),[12] char(1),[13] char(1),[14] char(1),[15] char(1),[16] char(1),[17] char(1),[18] char(1),[19] char(1),[20] char(1),
[21] char(1),[22] char(1),[23] char(1),[24] char(1),[25] char(1),[26] char(1),[27] char(1),[28] char(1),[29] char(1),[30] char(1),
[31] char(1),[32] char(1),[33] char(1),[34] char(1),[35] char(1),[36] char(1),[37] char(1),[38] char(1),[39] char(1),[40] char(1),
[41] char(1),[42] char(1),[43] char(1),[44] char(1),[45] char(1),[46] char(1),[47] char(1),[48] char(1),[49] char(1),[50] char(1),
[51] char(1),[52] char(1),[53] char(1),[54] char(1),[55] char(1),[56] char(1),[57] char(1),[58] char(1),[59] char(1),[60] char(1),
[61] char(1),[62] char(1),[63] char(1),[64] char(1),[65] char(1),[66] char(1),[67] char(1),[68] char(1),[69] char(1),[70] char(1),
[71] char(1),[72] char(1),[73] char(1),[74] char(1),[75] char(1),[76] char(1),[77] char(1),[78] char(1),[79] char(1),[80] char(1),
[81] char(1),[82] char(1),[83] char(1),[84] char(1),[85] char(1),[86] char(1),[87] char(1),[88] char(1),[89] char(1),[90] char(1),
[91] char(1),[92] char(1),[93] char(1),[94] char(1),[95] char(1),[96] char(1),[97] char(1),[98] char(1),[99] char(1),[100] char(1),
[101] char(1),[102] char(1),[103] char(1),[104] char(1),[105] char(1),[106] char(1),[107] char(1),[108] char(1),[109] char(1),[110] char(1),
[111] char(1),[112] char(1),[113] char(1),[114] char(1),[115] char(1),[116] char(1),[117] char(1),[118] char(1),[119] char(1),[120] char(1),)--分列代码
declare @i tinyint,@str0 varchar(500),@tsql varchar(600)
declare ap scroll cursor for select dt from 铜川成绩$open ap
fetch first from ap into @str0
while(@@FETCH_STATUS<>-1)
begin
select @i=1,@tsql=null
while(@i<=120)
begin
select @tsql=ISNULL(@tsql+',','select ')+''''+SUBSTRING(@str0,@i,1)+''' '
select @i=@i+1
end
insert into #1123 exec(@tsql)
fetch next from ap into @str0
endclose ap
deallocate ap这是我的代码,原数据一行120个数字我查的很清楚了,就是报错:列名或所提供值的数目与表定义不匹配。
1.将declare @i tinyint,@str0 varchar(500),@tsql varchar(600) 修改为
declare @i int,@str0 varchar(6000),@tsql varchar(6000) 试试.
2.检查[铜川成绩$]表dt列的数据是否正常.再不行可以私信找我,远程帮你看看..
1.将declare @i tinyint,@str0 varchar(500),@tsql varchar(600) 修改为
declare @i int,@str0 varchar(6000),@tsql varchar(6000) 试试.
2.检查[铜川成绩$]表dt列的数据是否正常.再不行可以私信找我,远程帮你看看..
谢版主大大,已经搞定。