Post reply

variable column alias

  • July 19, 2004 at 3:43 pm

    #108792

    Is there a way to use a variable as a column alias?  What I would like to do is produce a column alias of datename(dw,@datefield).  Is there a way to do this?

  • July 19, 2004 at 5:40 pm

    #515136

    You can use a dynamic query.

    Try something like this:

    create procedure sp_test

    @datefield varchar(20)

    as

    set nocount on

    declare @queryString varchar(200)

    set @queryString = 'select '+datename(dw,@datefield)+' = ''a value'''

    execute(@queryString)

    execute sp_test '07/19/04'

    execute sp_test '07/20/04'

    execute sp_test '07/21/04'

    You should get the alias 'Monday', 'Tuesday' & 'Wednesday' (with 'a value' as the value)

  • July 20, 2004 at 1:22 am

    #515165

    What is your overall goal?

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 20, 2004 at 5:59 am

    #515211

    I am trying to create a report with a column heading of the day of the week but with a column value of the sum of a field for that date.

  • July 20, 2004 at 6:06 am

    #515216

    So, you're doing a crosstab report? Shouldn't the heading be the job of your front-end?

    Here is one of the most-referenced tools for this:

    http://www.rac4sql.com

    Shouldn't be too difficult to combine the result with DATENAME to get what your after.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 20, 2004 at 6:19 am

    #515224

    Thanks for pointing me in the right direction.

  • July 20, 2004 at 8:12 am

    #515270

    SET DATEFIRST 7  --Set First Day of Week to Sunday

    SELECT SUM(CASE WHEN DATEPART(weekday,[date]) = 1 THEN [column] ELSE 0 END) AS 'Sun',

     SUM(CASE WHEN DATEPART(weekday,[date]) = 2 THEN [column] ELSE 0 END) AS 'Mon',

     SUM(CASE WHEN DATEPART(weekday,[date]) = 3 THEN [column] ELSE 0 END) AS 'Tue',

     SUM(CASE WHEN DATEPART(weekday,[date]) = 4 THEN [column] ELSE 0 END) AS 'Wed',

     SUM(CASE WHEN DATEPART(weekday,[date]) = 5 THEN [column] ELSE 0 END) AS 'Thu',

     SUM(CASE WHEN DATEPART(weekday,[date]) = 6 THEN [column] ELSE 0 END) AS 'Fri',

     SUM(CASE WHEN DATEPART(weekday,[date]) = 7 THEN [column] ELSE 0 END) AS 'Sat'

    FROM 

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply