我有如下数据: ID Name Sdate 1 aa 2009-01-01 2 bb 2009-01-01 3 cc 2009-01-01 如何select出结果,当然不会有人提这个问题的,这只是一个假设
使用本过程:
declare@svarchar(8000) set@s='Ta ID Name Sdate 1 aa 2009-01-01 2 bb 2009-01-01 3 cc 2009-01-01 ' exec sp_autoscript @s
就可以生成如下代码:
/**//* -- Author:Flystone -- Date:2008-05-15 -- Version:V1.001 */ -- Test Data: Ta Ifobject_id('Ta') isnotnull Droptable Ta Go Createtable Ta(ID int,Name varchar(2),Sdate smalldatetime) Go Insertinto Ta select1,'aa','2009-01-01'unionall select2,'bb','2009-01-01'unionall select3,'cc','2009-01-01' Go --Start Select*from Ta --Result: /**//* */ --End
--处理当前串中的空格,准备加入对时分秒的处理, 时间末处理。。。。。。 createfunction f_castdt(@svarchar(8000)) returnsvarchar(1000) as begin set@s=ltrim(@s) WHILECHARINDEX('',@s)>0 BEGIN SET@s=replace(@s,'','') END return@s end go --加一个分隔函数: createfunction F_split( @svarchar(8000), --包含多个数据项的字符串 @posint, --要获取的数据项的位置 @splitvarchar(10) --数据分隔符 )RETURNSvarchar(100) AS BEGIN IF@sISNULLRETURN(NULL) DECLARE@splitlenint--分隔符长度 SELECT@splitlen=LEN(@split+'a')-2 WHILE@pos>1ANDcharindex(@split,@s+@split)>0 SELECT@pos=@pos-1, @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'') RETURN (replace((nullif(left(@s,charindex(@split,@s+@split)-1),'')),char(13),'')) END GO --判断当前串是有字母如果有那么当作varchar createfunction f_ischar(@svarchar(100)) returnsbit as begin ifpatindex('%[a-zA-Z]%',@s)>0 return1 return0 end go -- 循环截取法 CREATEFUNCTION f_splitSTR( @svarchar(8000), --待分拆的字符串 @splitvarchar(20) --数据分隔符 )RETURNS@reTABLE(col varchar(100)) AS BEGIN DECLARE@splitlenint SET@splitlen=LEN(@split+'a')-2 WHILECHARINDEX(@split,@s)>0 BEGIN INSERT@reVALUES( rtrim(ltrim(LEFT(@s,CHARINDEX(@split,@s)-1))) ) SET@s=ltrim(STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')) END INSERT@reVALUES(@s) RETURN END GO createproc sp_autoscript @svarchar(8000) as set nocount on declare@avarchar(8000),@bvarchar(8000),@cvarchar(8000) set@a=ltrim(rtrim(left(@s,charindex(char(13),@s) -1))) set@s=right(@s,len(@s) -charindex(char(13),@s)-1) set@b=ltrim(rtrim(left(@s,charindex(char(13),@s) -1))) set@s=right(@s,len(@s) -charindex(char(13),@s)-1) set@c=ltrim(rtrim(left(@s,charindex(char(13),@s) -1))) set@s=left(@s,len(@s) -1) createtable #tmp1(px intidentity(1,1),col varchar(120),col2 varchar(50),col3 varchar(50)) createtable #tmp2(px intidentity(1,1),col varchar(120)) createtable #tmp3(px intidentity(1,1),col varchar(1000)) insertinto #tmp1(col) selectltrim(rtrim(col)) from f_splitSTR(@b,'') insertinto #tmp2 selectltrim(rtrim(col)) from f_splitSTR(@c,'') insertinto #tmp3 selectltrim(rtrim(col)) from f_splitSTR(@s,' ') --取每一列的最大值,尽量让类型准确 --update a --set col= c.col --from #tmp2 a left join ( --select a.px, max(dbo.F_split(b.col,a.px,' ')) as col --from #tmp2 a,#tmp3 b --group by a.px) c on a.px = c.px declare@pxint,@colvarchar(100),@maxidint select@maxid=count(1) from #tmp1 DECLARE f CURSORFORSELECT px,col FROM #tmp2 OPEN f FETCHNEXTFROM f INTO@px,@col --IF @@FETCH_STATUS <> 0 WHILE@@FETCH_STATUS=0 BEGIN ifISDATE(@col) =1 iflen(@col) <=10 update #tmp1 set col = col +' smalldatetime,', col2 =casewhen px =1then' select '''''else''''''end, col3 =casewhen px =@maxidthen''''' union all'else''''','end where px =@px else update #tmp1 set col = col +' datetime,', col2 =casewhen px =1then' select '''''else''''''end, col3 =casewhen px =@maxidthen''''' union all'else''''','end where px =@px else ifisnumeric(@col) =1 ifcharindex('.',@col) >0 update #tmp1 set col = col +' numeric('+ltrim(len(@col) -1)+','+ltrim(len(@col) -charindex('.',@col))+'),', col2 =casewhen px =1then' select 'else''end, col3 =casewhen px =@maxidthen' union all'else','end where px =@px else update #tmp1 set col = col +' int,', col2 =casewhen px =1then'select 'else''end, col3 =casewhen px =@maxidthen' union all'else','end where px =@px else if dbo.f_ischar(@col) =1 update #tmp1 set col = col +' varchar('+ltrim(len(@col))+'),', col2 =casewhen px =1then' select '''''else''''''end, col3 =casewhen px =@maxidthen''''' union all'else''''','end where px =@px FETCHNEXTFROM f INTO@px,@col END CLOSE f DEALLOCATE f update #tmp3 set col = dbo.f_castdt(col) declare@sqlvarchar(8000) declare@sql1varchar(8000) select@sql=isnull(@sql+'+' ,'')+'''' +col2 +'''+dbo.F_split(col,'+ltrim(px)+','''')'+'+'''+col3+'''' from #tmp1 select@sql1=isnull(@sql1,'') + col from #tmp1 --select @sql --if object_id('''+@a+''') is not null drop table '+@a+'+char(13)+ exec('update #tmp3 set col = '+@sql) set@sql='' set@sql='/* -- Author:Flystone -- Date:2008-05-15 -- Version:V1.001 */'+char(10)+ ' -- Test Data: '+@a+char(10)+ 'If object_id('''+@a+''') is not null '+char(13)+ ' Drop table '+@a+char(10)+ 'Go'+char(13)+ 'Create table '+@a+'('+left(@sql1,len(@sql1) -1)+')'+char(10)+ 'Go' +char(10)+ 'Insert into '+@a+' ' select@sql=@sql+ col +char(13) from #tmp3 set@sql=left(@sql,len(@sql) -10) set@sql=@sql+char(13) + 'Go'+char(10)+ '--Start'+char(10)+ 'Select * from '+@a+char(10)+' --Result: /* */ --End ' print@sql droptable #tmp1,#tmp2,#tmp3 set nocount off go ---- 调用 declare@svarchar(8000) set@s='Ta ID Name Sdate 1 aa 2009-01-01 2 bb 2009-01-01 3 cc 2009-01-01 ' exec sp_autoscript @s ---- 清除 dropfunction f_splitSTR,f_ischar,f_castdt,F_split dropproc sp_autoscript