none
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
    Moderator
  • 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
    Moderator

All replies