locked
Concatenate a string to use after the AS statement RRS feed

  • Question

  • I am trying to create dynamic column headings and I can't be the only person that has wanted to do this.

    The previous person created this report with hardcoded dates and naturally it quit working when he left because he was no longer replacing the dates.  I rewrote it with a variable but can't figure out how to make the column headings as dynamic as the returned data. I don't want to resort to headings like "Jan of current year" Or "Current year + 1"

    I thought I could do

    '$'+CONVERT(varchar,CONVERT(MONEY,m.Jan),1) AS concat ('Jan' + @Current Year) ,

    where @Current year holds the char value for the current year.  But SQL 2005 is pitching a fit about  Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'Jan'.

    Any Ideas?

    Tuesday, August 3, 2010 1:58 PM

Answers

  • You can follow the sample below. Let us know if helpful.

    -- SQL Server dynamic SQL example
    DECLARE @DynamicSQLString nvarchar(max)
    DECLARE @CurrentYear char(4) = convert(char(4),year(getdate()))
    
    SET @DynamicSQLString= 'SELECT SUM(TotalDue) AS [Total Sales as of Year: '
                +@CurrentYear+'] '+
           CHAR(13)+ 'FROM AdventureWorks2008.Sales.SalesOrderHeader'+
           CHAR(13)+ 'WHERE Year(OrderDate)> 2000'
                 
    PRINT @DynamicSQLString -- test & debug
    /*
    SELECT SUM(TotalDue) AS [Total Sales as of Year: 2010] 
    FROM AdventureWorks2008.Sales.SalesOrderHeader
    WHERE Year(OrderDate)> 2000
    */
    EXEC sp_executeSQL @DynamicSQLString
    /* Total Sales as of Year: 2010
    123216786.1159
    */
     
    

    Kalman Toth, SQL Server & Business Intelligence Training;  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Tuesday, August 3, 2010 2:27 PM
  • Normally, T-SQL does not allow variable names in column aliases, nor does it allow you to use expressions, such as concatenation, to derive the column name.

    With dynamic SQL, you are basically building your SQL Statement on the fly. It is not a stored procedure. You can think of it like a pre-processor. It resolves your variables, performs the concatenation and generates a legal T-SQL statement, which is then executed.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, August 3, 2010 2:26 PM

All replies

  • You have to use dynamic SQL.

    Dynamic SQL links with examples:

    http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth, SQL Server & Business Intelligence Training;  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    • Edited by Kalman Toth Thursday, November 9, 2017 3:48 PM
    Tuesday, August 3, 2010 2:01 PM
  • You will have to create your statement dynamically.

    Try to get familiar with the pros and cons of this useful feature, before jumping to write dynamic sql.

    The Curse and Blessings of Dynamic SQL

     

    AMB

    Tuesday, August 3, 2010 2:03 PM
  • So when you say Dynamic SQL you mean SQL that creates a stored proc that will populate something in advance with column headings? I apologize that I do not know what the dynamic part will change in generating these column names.
    Tuesday, August 3, 2010 2:12 PM
  • When we say "dynamic sql", we mean that you will have to create the statement dynamically and then execute it using "exec(...)" or "sp_executesql".

    Here is an example that you can revise to get familiar with the concept.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/34d721a3-53bf-4436-8dd0-90b65cadee59/

     

    AMB

    Tuesday, August 3, 2010 2:17 PM
  • Normally, T-SQL does not allow variable names in column aliases, nor does it allow you to use expressions, such as concatenation, to derive the column name.

    With dynamic SQL, you are basically building your SQL Statement on the fly. It is not a stored procedure. You can think of it like a pre-processor. It resolves your variables, performs the concatenation and generates a legal T-SQL statement, which is then executed.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, August 3, 2010 2:26 PM
  • You can follow the sample below. Let us know if helpful.

    -- SQL Server dynamic SQL example
    DECLARE @DynamicSQLString nvarchar(max)
    DECLARE @CurrentYear char(4) = convert(char(4),year(getdate()))
    
    SET @DynamicSQLString= 'SELECT SUM(TotalDue) AS [Total Sales as of Year: '
                +@CurrentYear+'] '+
           CHAR(13)+ 'FROM AdventureWorks2008.Sales.SalesOrderHeader'+
           CHAR(13)+ 'WHERE Year(OrderDate)> 2000'
                 
    PRINT @DynamicSQLString -- test & debug
    /*
    SELECT SUM(TotalDue) AS [Total Sales as of Year: 2010] 
    FROM AdventureWorks2008.Sales.SalesOrderHeader
    WHERE Year(OrderDate)> 2000
    */
    EXEC sp_executeSQL @DynamicSQLString
    /* Total Sales as of Year: 2010
    123216786.1159
    */
     
    

    Kalman Toth, SQL Server & Business Intelligence Training;  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Tuesday, August 3, 2010 2:27 PM
  • Thank you. I was missing that you just build a string with the first part and invoke it on the second. I appreciate your help.
    Tuesday, August 3, 2010 5:33 PM
  • I just used this for a similar problem.
    Thanks for the great answer!

    -Al H

    Thursday, January 17, 2019 9:31 PM