How to create/add columns using a variable in a loop

Question

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

This is usually not something you would ever do as the users of a schema generally prefer fixed table definitions (not dynamic ones with a variable number of columns based on a date range). However you can construct a sql string (declare @sql varchar(2000)), populate it with an alter statement, and then run it using EXEC or sp_executesql — Igor
– Igor, Commented Jul 6, 2016 at 20:20
What do you mean by "header"? Is that the column name? You do NOT want to use dates as column names. Not only is that painful to work with it is a sign of a very poor design. Also, consider how many columns it would be to have a column for every day between today and 2046. You would exceed the maximum number of columns by several orders of magnitude. — Sean Lange
– Sean Lange, Commented Jul 6, 2016 at 20:21
please give us a use case so we can show you how to make a better design. — Hogan
– Hogan, Commented Jul 6, 2016 at 20:31
@Sean Lange Yes, by column header I mean column name. I wouldn't actually need it to go from now to 2046 but I would have it go from the beginning of one year to the end of that same year. Plus I only need 52 columns for each table. I don't really know the limitations of SQL Server as I'm still very new but thank you for the comment, it got me thinking. — Lacuna
– Lacuna, Commented Jul 6, 2016 at 20:31
You don't want dates as column names. That indicates there are some serious normalization problems. Since you stated 52 I am guessing you want data for each week of a year? Normally you would have each week be a row in the table, not another column. — Sean Lange
– Sean Lange, Commented Jul 6, 2016 at 20:33

Matt · Accepted Answer · 2016-07-06 20:32:38Z

1

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

answered Jul 6, 2016 at 20:32

Matt

14.4k2 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create/add columns using a variable in a loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related