tablename fieldname isdatetime datetimelength
-------------------------------------------------- -------------------------------------------------- ---------- --------------
Ids_ActualData sw1 0 NULL
Ids_ActualData sw2 0 NULL
Ids_ActualData ymdhm 1 16
Ids_Configuration zdID 0 NULL
Ids_Configuration zdmc 0 NULL
Ids_ActualData yl 0 NULL
我这里有张这样的表,我想把表内的字段通过sql转换成
Ids_Configuration.zdID zdid,Ids_Configuration.zdmc zdmc,convert(varchar(16),Ids_ActualData.ymdhm,120) ymdhm,Ids_ActualData.sw1 sw1,Ids_ActualData.sw2 sw2
其中convert(varchar(16),Ids_ActualData.ymdhm,120)是通过isdatetime来判断的,这里面的16就是datetimelength里存储的,请高手帮忙啊,谢谢
解决方案 »
- 远程备份
- 一个where in 的简单问题~!
- 如何在SQL Server 2000中打开quicken payroll Premier的数据库?
- sql server2005 数据库文件和日志文件被删除,怎样把数据库里该数据库删除
- 我用的是路由器上的ip地址进行远程连接sqlserver2000的服务器,可是提示“sql server 不存在或拒绝访问”
- *************暴难的问题(续)******************
- 如何用存储过程实现下面的功能
- 程序安装时能实现建库,设置登陆帐号,如何写安装程序?
- 如何写获取连续三个月销售额都大于一定的shop的sql语句
- 求救!关于access和sql server中的子表操作!
- 求一复杂查询,100送
- 有关时间取值的问题
insert into tb values('Ids_ActualData ' , 'sw1' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'sw2' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'ymdhm', 1 , 16)
insert into tb values('Ids_Configuration' , 'zdID' , 0 , NULL)
insert into tb values('Ids_Configuration' , 'zdmc' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'yl' , 0 , NULL)
godeclare @tablename varchar(20), @fieldname varchar(20), @isdatetime int,@datetimelength int;
declare @sql as varchar(8000)
set @sql = ''
declare cur cursor fast_forward for
select * from tb;
open cur;
fetch next from cur into @tablename , @fieldname , @isdatetime ,@datetimelength;
while @@fetch_status=0
begin
if @isdatetime = 1
set @sql = @sql + 'convert(varchar(' + cast(@datetimelength as varchar) + '),' + @tablename + '.' + @fieldname + ',120) ' + @fieldname + ','
else
set @sql = @sql + @tablename + '.' + @fieldname + ' ' + @fieldname + ','
fetch next from cur into @tablename , @fieldname , @isdatetime ,@datetimelength;
end
close cur;
deallocate cur;set @sql = left(@sql , len(@sql) - 1)
print @sql drop table tb/*
Ids_ActualData .sw1 sw1,Ids_ActualData .sw2 sw2,convert(varchar(16),Ids_ActualData .ymdhm,120) ymdhm,Ids_Configuration.zdID zdID,Ids_Configuration.zdmc zdmc,Ids_ActualData .yl yl
*/
create table tb(tablename varchar(20), fieldname varchar(20), isdatetime int,datetimelength int)
insert into tb values('Ids_ActualData ' , 'sw1' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'sw2' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'ymdhm', 1 , 16)
insert into tb values('Ids_Configuration' , 'zdID' , 0 , NULL)
insert into tb values('Ids_Configuration' , 'zdmc' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'yl' , 0 , NULL)
godeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + t.tablename + '.' + t.fieldname + ' ' + t.fieldname + ',' from (select * from tb where isdatetime = 0) as t
select @sql = @sql + 'convert(varchar(' + cast(t.datetimelength as varchar) + '),' + tablename + '.' + fieldname + ',120) ' + fieldname + ',' from (select * from tb where isdatetime = 1) as t
set @sql=left(@sql , len(@sql) - 1)
print @sql drop table tb/*
Ids_ActualData .sw1 sw1,Ids_ActualData .sw2 sw2,Ids_Configuration.zdID zdID,Ids_Configuration.zdmc zdmc,Ids_ActualData .yl yl,convert(varchar(16),Ids_ActualData .ymdhm,120) ymdhm
*/