如何在循环中使用变量创建/添加列 [英] How to create/add columns using a variable in a loop

问题描述

我对SQL还是很陌生，因为我刚刚读完 Sams在10分钟内自学SQL ，这是我唯一的SQL知识。因此，既然我已经完成了这本书，那么我正在尝试创建一些表格，以便可以使用它们。我可以轻松地创建具有已知列数和指定标题的表。我遇到麻烦的地方是创建一个表，该表的列数未知，并且日期作为标题。到目前为止，我一直在尝试以下方法：

  DECLARE @start_date AS DATE 
 DECLARE @end_date AS DATE 
 DECLARE @curr_date AS日期（
声明@column_name）AS CHAR（10）
 
 SET @开始日期= 2016-01-02 
 SET @结束日期= 2016-12-31 
 SET @ curr_date = @start_date 
 
 @curr_date< @end_date 
 SET @curr_date = DATEADD（DD，7，@curr_date）
 ALTER TABLE Project_1 
添加@curr_date DOUBLE 
  

我在这里想要做的是为循环创建一个起点和终点，并使用存储在局部变量中的循环条件作为我的列标题，因为这是我需要的列被冠以标题。我还尝试使用 CAST 将其转换为字符，但DBMS很高兴让我知道'@curr_date'附近的语法不正确在最后一行（ADD行）上，因为它不喜欢我试图用局部变量命名列（我认为）。我没有示例输出，但是输出应该是一个表，第一列定义为CHAR，标题为 emp_name ，因为它将保留名称。定义为 DOUBLE 类型的所有其他列，并且应为 NULL ，因为它们将保持多个小时并且必须具有当前日期作为标题 @curr_date 。我认为通过 ALTER 方法添加到表中的所有列无论如何都默认为 NULL 。我已经看到了动态SQL的示例，在这些示例中，您声明了一个变量来保存select语句，但是我不太了解它们如何将列添加到表中。我不确定是否可以在 CREATE 语句中完成此操作，但是如果可以的话，那真是太好了。另外，这需要是可变的，因为我可以将 @end_date 更改为……他们是2046年。

这里的安全性不是问题

解决方案

我同意所有有关使用ROW而不动态添加列的注释，您可以始终动态添加列枢纽那些后者，它将更加灵活。因此，也许有一些架构方面的考虑，但只是为了回答您的特定问题，您在什么地方关闭..... b $ b DECLARE @结束日期AS DATE
DECLARE @curr_date AS日期
DECLARE @column_name AS CHAR（10）

SET @start_date ='2016-01-02'
SET @end_date ='2016-12-31'
SET @curr_date = @开始日期

@curr_date< @end_date
开始
声明@SQL NVARCHAR（MAX）

SET @curr_date = DATEADD（DD，7，@curr_date）

SET @SQL ='ALTER TABLE TableB
ADD ['+ CAST（@curr_date AS VARCHAR（10））+'] FLOAT'

--PRINT @SQL
EXECUTE（@SQL）
END

I am very new to SQL in that I just finished reading Sams Teach Yourself SQL in 10 Minutes and that is my only SQL knowledge. So now that I'm done with the book, I'm trying to create some tables so I can play around with them. I can easily create a table with a known amount of columns and specified header. Where I am having trouble is creating a table with an unknown amount of columns and a date as the header. What I have tried so far is this:

DECLARE @start_date AS DATE
DECLARE @end_date AS DATE
DECLARE @curr_date AS DATE
DECLARE @column_name AS CHAR(10)

SET @start_date = 2016-01-02
SET @end_date = 2016-12-31
SET @curr_date = @start_date

WHILE @curr_date < @end_date
    SET @curr_date = DATEADD(DD, 7, @curr_date)
    ALTER TABLE Project_1
        ADD @curr_date  DOUBLE

What I tried to do here is make a start and end point for the loop and use the loop condition which is stored in a local variable as my column header since that is what I need the column to be titled. I also tried using CAST to cast it as a char but the DBMS is delighted to let me know that there is Incorrect syntax near '@curr_date' on that last line (the ADD line) because it doesn't like that I'm trying to name a column with a local variable (I think). I don't have a sample output but the output should be a table with the first column defined as CHAR and titled emp_name because it will be holding names. All other columns defined as type DOUBLE and should be NULL because they will be holding a number of hours and must have the current date as the header @curr_date. I think all columns added to a table through the ALTER method are defaulted to NULL anyways. I've seen examples of dynamic SQL where you declare a variable to hold the select statement but I don't really understand how they add columns to a table. I'm not sure if this can be done in the CREATE statement but if it can that would be great to see. Also, this needs to be variable in the fact that I could change the @end_date to lets say... they year 2046.

Security is not an issue here

解决方案

I agree with all of the comments about using ROWs not dynamically adding columns you can always dynamically pivot those latter and it will have more flexibily. So maybe some schema considerations but just to answer your specific question you where close.....

DECLARE @start_date AS DATE
DECLARE @end_date AS DATE
DECLARE @curr_date AS DATE
DECLARE @column_name AS CHAR(10)

SET @start_date = '2016-01-02'
SET @end_date = '2016-12-31'
SET @curr_date = @start_date

WHILE @curr_date < @end_date
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET @curr_date = DATEADD(DD, 7, @curr_date)

    SET @SQL = 'ALTER TABLE TableB
        ADD [' + CAST(@curr_date AS VARCHAR(10)) + ']  FLOAT'

    --PRINT @SQL
    EXECUTE (@SQL)
END

这篇关于如何在循环中使用变量创建/添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！