1

Is there any way I can add a column to a table but I want the heading to be a date, and every new column added will have a column heading for the next day hence the 

SET @date1 = @date1 + 1

What I want the table to look like is, where the date on top is a new column for each day the script loops:

StoreID StoreName     02/01/12    03/01/12    04/01/12
1234  Coles1         7512       8574        
1235  Coles2         7210       8441
1236  Coles3         4845       5448

When I run the script I get the following error messages:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@Column'.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@Column'.

Here is my script:

DECLARE @date datetime
DECLARE @date1 datetime
DECLARE @date2 datetime
DECLARE @Column varchar(8)
SET @date = '02 Jan 2012'
SET @date1 = '02 Jan 2012'
SET @date2 = '08 Jan 2012'
SET @Column = CONVERT(VARCHAR(8), @date1, 3)

IF NOT EXISTS (SELECT * FROM sysobjects WHERE xtype = 'U' AND name = '#vl_temp_trans') 
BEGIN
   CREATE TABLE #vl_temp_trans
      (StoreID INT,
       StoreName VARCHAR(100),
       @Column MONEY)                  ----> column name to be date "@Column)
END

WHILE (@date1 <= @date2)
BEGIN
   SET @Column =  CONVERT(VARCHAR(8), @date1, 3)

   ALTER table #vl_temp_trans
   ADD @Column MONEY     ----> column name to be date "@Column" 

   Insert into #vl_temp_trans (storeID, storeName, @Column)
      select storeId, storeName, TotalDailyTransactions
      from daily_trans t1 (nolock) 
      full outer join outlets t2 (nolock) on t1.StoreID = t2.StoreID 
      where DailyEnd = @date1 + 1

   SET @date1 = @date1 + 1
END
Improve this question
6
  • Do you know why I made edits to your question? Why did you undo them? Commented Feb 28, 2012 at 4:48
  • 1
    Also formatting and readability aside, which table has the columns TotalDailyTransactions and DailyEnd? One, both? Why aren't any of your columns prefixed with their table alias to avoid ambiguous column name errors (and questions from us)? Commented Feb 28, 2012 at 5:03
  • check updated code...that might resolve your issue..... Commented Feb 28, 2012 at 5:14
  • Hi Aaron, thank for your responses, but I didn't undo anything... Commented Feb 28, 2012 at 5:29
  • 1
    You've made two edits to the code after I applied proper code formatting. I'm not going to bother doing it again but please highlight error messages, tabular data etc. and click the code {} button to make them look like your code sample. Commented Feb 28, 2012 at 5:41

3 Answers 3

Reset to default
2

You can't do this without dynamic SQL. Here is a query that will get you the result you want. You are more than welcome to uncomment the --INTO #t bit, however it is unclear what you want to do with the #temporary table beyond that (if you tell us the end result, instead of "I want to add a column name as @column, maybe we can help with that too). In order to continue referencing that #t table, you'll need to continue using code within the same scope - meaning more dynamic SQL that is executed within the same sp_executesql call.

DECLARE 
    @start DATE = '2012-01-02',
    @end   DATE = '2012-01-08';

DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @colMax   NVARCHAR(MAX) = N'',
    @colNames NVARCHAR(MAX) = N'';

;WITH x(rn) AS ( SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) ROW_NUMBER()
  OVER (ORDER BY [object_id]) - 1 FROM sys.all_columns ), 
 y(d) AS ( SELECT CONVERT(CHAR(10), DATEADD(DAY, rn, @start)) FROM x 
)
SELECT @colMax += N',' + CHAR(13) + CHAR(10) 
     + QUOTENAME(d) + ' = SUM(CASE WHEN DailyEnd = ''' 
     + d + ''' THEN TotalDailyTransactions ELSE 0 END)',
    @colNames += N',' + QUOTENAME(d) FROM y;

SET @sql = 'SELECT StoreID, StoreName, ' + STUFF(@colNames, 1, 1, '')
    + ' --INTO #t 
        FROM ( SELECT StoreID, StoreName, ' + STUFF(@colMax, 1, 1, '')
    + ' FROM dbo.daily_trans
        WHERE DailyEnd >= ''' + CONVERT(CHAR(10), @start) + ''''
    + ' AND DailyEnd < ''' + CONVERT(CHAR(10), DATEADD(DAY, 1, @end)) + '''
        GROUP BY StoreID, StoreName
        UNION ALL SELECT StoreID, StoreName, ' + STUFF(@colMax, 1, 1, '')
    + ' FROM dbo.outlets
        WHERE DailyEnd >= ''' + CONVERT(CHAR(10), @start) + ''''
    + ' AND DailyEnd < ''' + CONVERT(CHAR(10), DATEADD(DAY, 1, @end)) + '''
        GROUP BY StoreID, StoreName) AS x';

PRINT @sql;
-- EXEC sp_executesql @sql;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Aaron, thank you for your help, but I've got the below error messages: Msg 139, Level 15, State 1, Line 2 Cannot assign a default value to a local variable. Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'MAX'. Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near '('. Msg 137, Level 15, State 2, Line 2 Must declare the variable '@start'. Msg 137, Level 15, State 2, Line 2 Must declare the variable '@colMax'. Msg 137, Level 15, State 2, Line 2 Must declare the variable '@colNames'. Msg 137, Level 15, State 2, Line 2 Must declare the variable '@sql'.
I can't read all that garbage in a comment, sorry. Did you copy and paste this code sample and run it independently, or did you edit it or mix it in with some of your other script?
Can you show a screen shot? Because I am running it here (both against a 2008 R2 database and a database in 2005 compat mode) and not getting any errors. Are you sure you're using SQL Server 2008?
0

I think the problem is that you can't use a variable to define a column name.

I used some of your code to test with. This first part executed fine.

DECLARE @date1 datetime
DECLARE @Column varchar(8)
SET @date1 = '02 Jan 2012'
SET @Column = CONVERT(VARCHAR(8), @date1, 3)
select @Column

But when I added the CREATE TABLE statement and executed all of it at once, then I got the same error you did.

CREATE TABLE #vl_temp_trans
(StoreID INT,
StoreName VARCHAR(100),
@Column MONEY)                  ----> column name to be date "@Column)

To do this you will need to build up the CREATE TABLE and ALTER TABLE statements as a string and then execute those using EXECUTE or sp_executesql. A search for "dynamic sql" will also give you some article that describe this too.

Improve this answer

Comments

0

There is two things you need to do to execute this code...

  1. Create dynamic sql make use of SP_ExecuteSQl
  2. you need to make your temp table Gobal because when you create private temp table it remain in scope of dyanic sql sp only

have look to below code which is updated by me sure reslove your issue 

DECLARE @date datetime 
DECLARE @date1 datetime 
DECLARE @date2 datetime 
DECLARE @ColumnNAAME varchar(8) 
Declare @Query NVARCHAR(1000)
DECLARE @ParmDefinition NVARCHAR(500);


SET @date = getdate() 
SET @date1 = getdate() 
SET @date2 = getdate() 
SET @ColumnNAAME = CONVERT(VARCHAR(8), @date1, 3)  
IF NOT EXISTS (SELECT * FROM sysobjects WHERE xtype = 'U' AND name = '#vl_temp_trans')      
BEGIN      
SET @ParmDefinition = N'@Column varchar(8)';   
Set @Query = 'CREATE TABLE ##vl_temp_trans  (StoreID INT,     StoreName VARCHAR(100),     ['+@ColumnNAAME+'] MONEY)' 

EXECUTE sp_executesql  @Query,@ParmDefinition,
                      @Column = @ColumnNAAME;

                 ----> column name to be date "@Column)      
SELECT * from  ##vl_temp_trans

END 
ELSE 
BEGIN
    SELECT * from  ##vl_temp_trans
END
Improve this answer

7 Comments

Hi, Thank you for your response, but the column name is not showing the date, it's showing @column.
Also I highly recommend against ##global temporary tables unless you really want concurrency = 1 (and can enforce it somehow) or you don't mind two users mixing their results together.
@Sanjay, what does this example do? All it does for me is create a ##global temp table with a single date column and return an empty result set. The table should have all the rows from the join pivoted on all the dates in the range - so in this case there should be 9 columns in the #temp table.
Asper requirement have to create only ##Global Temp table.. I have give only sample you have to complete code and execuate..
Where did the user say they needed a ##global temp table? Do you understand the different between a #local and ##global temp table?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.