none
column alias as variable

    Question

  • Is there a way to select a column as an alias using a variable for the alias?  something like this:

    SELECT  Column1 as @myVariable  FROM Table1

    Wednesday, June 28, 2006 7:21 PM

Answers

  • Not directly in a SELECT statement. You can use dynamic SQL like:
     
    set @sql = N'SELECT Column1 as ' + quotename(@myVariable) + N' FROM Table1'
    exec sp_executesql @sql
     
    But it is not going to be pretty if you want to do this for multiple columns and dynamic SQL has security implications/management issues. Why do you want to do this? How will the client handle this if the column names can be modified arbitrarily? One way to do this is to fix the column names as c1, c2, c3 etc and have a separate result set or metadata that contains the user friendly names for c1, c2, c3 respectively.
    Wednesday, June 28, 2006 7:33 PM

All replies

  • Not directly in a SELECT statement. You can use dynamic SQL like:
     
    set @sql = N'SELECT Column1 as ' + quotename(@myVariable) + N' FROM Table1'
    exec sp_executesql @sql
     
    But it is not going to be pretty if you want to do this for multiple columns and dynamic SQL has security implications/management issues. Why do you want to do this? How will the client handle this if the column names can be modified arbitrarily? One way to do this is to fix the column names as c1, c2, c3 etc and have a separate result set or metadata that contains the user friendly names for c1, c2, c3 respectively.
    Wednesday, June 28, 2006 7:33 PM
  • The user enters a period length such as 30 days and the report displays the period date ranges as the coulmn name of a pivot.

    like this:

    Location  1-30   31-60   61-90

    Texas      10         3            2

    Florida     5           8           7

     

    Wednesday, June 28, 2006 7:38 PM
  • i agree with jschroder because of i need the simular query.
    i want to write the week numbers to the column names. it will be changing every week.
    like

    stock name           50.week     51.week       52.week      53.week
    stok1                     1000            500              450            900
    .
    .
    .
    .
    .
    .

    like this
    Thursday, December 10, 2009 1:47 PM