如何根据开始日期和结束日期在sql server 2008中自动创建列 [英] How to create columns automatically in sql server 2008 based on start date and end date
本文介绍了如何根据开始日期和结束日期在sql server 2008中自动创建列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何根据开始日期和结束日期在sql server 2008中自动创建列
这是我的表
创建 表降级
(
Id int primary key 身份( 1 , 1 ),
姓名 varchar ( 50 ),
StartDate date ,
EndDate date
)
声明 @ dates int , @ StartDate datetime , @ EndDate datetime
- - 在这里,我们假设,
选择 @ StartDate = 转换( datetime ,' 2015年1月7日'), @ endDate = 转换( datetime ,' 1 / 10/2015')
选择 @ dates = datediff (dd, @ StartDate , @ Enddate )
- - 现在使用while循环动态添加列
while ( @ dates > 0)
开始
alter table demotable add [Columnname] datetime
选择 @dates = @ dates - 1
结束
if i执行此代码时,一列(columnname)正在添加到我的sql表中并显示错误
*每个表中的列名必须是唯一的。表'demotable'中的列名'Columnname'被指定多次。
*
i想要从开始添加几个月的列日期到结束日期
解决方案
正如我在之前的回答中解释的那样:如何在每个下拉列表项目上创建新表选择 [ ^ ]这很可能是一件非常糟糕的事情。
但是,如果你因某种原因确实需要这样做,你必须确保每一栏命名不同。因此,动态构建ALTER TABLE命令并执行它。类似
声明 @ command < span class =code-keyword> nvarchar (max)
声明 @ dates int , @ StartDate datetime , @ EndDate datetime
- - - 在这里,我们假设,
选择 @ StartDate = convert ( datetime ,' 2015年1月7日'), @ endDate = convert ( datetime ,' 2015年1月10日')
选择 @ dates = datediff(dd, @ StartDate , @Enddate )
( @ dates > 0)开始
set @ command = ' alter table demotable add [Columnname' + cast( @dates as nvarchar (max))+ ' ] datetime'
exec sp_executesql @ command
选择 @ dates = @ dates - 1
end
但据说这个设计可能会导致问题将来
How to create columns automatically in sql server 2008 based on start date and end date
this is my table
create table demotable
(
Id int primary key identity(1,1),
Name varchar(50),
StartDate date,
EndDate date
)
declare @dates int, @StartDate datetime, @EndDate datetime
---Here, we assume that,
Select @StartDate = convert(datetime, '1/7/2015'), @endDate = convert(datetime,'1/10/2015')
select @dates = datediff (dd,@StartDate ,@Enddate)
---Now add columns by dynamically with while loop
While (@dates >0)
begin
alter table demotable add [Columnname] datetime
select @dates = @dates - 1
end
if i executes this code a column(columnname) is adding to my sql table with showing a error
*Column names in each table must be unique. Column name 'Columnname' in table 'demotable' is specified more than once.
*
i want to add no of months columns from start date to end date
解决方案
As I explained in previous answer: How to create new table on every drop down list item select[^] this is most likely a really bad thing to do.
However, if you really need to do this for some reason you must make sure that every column is named differently. So build the ALTER TABLE command dynamically and execute it. Something like
declare @command nvarchar(max) declare @dates int, @StartDate datetime, @EndDate datetime ---Here, we assume that, Select @StartDate = convert(datetime, '1/7/2015'), @endDate = convert(datetime,'1/10/2015') select @dates = datediff (dd,@StartDate ,@Enddate) While (@dates >0) begin set @command = 'alter table demotable add [Columnname' + cast(@dates as nvarchar(max)) + '] datetime' exec sp_executesql @command select @dates = @dates - 1 end
But as said this design will probably cause problems in the future.
这篇关于如何根据开始日期和结束日期在sql server 2008中自动创建列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文