Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

variable column alias Expand / Collapse
Author
Message
Posted Monday, July 19, 2004 3:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 7, 2012 2:15 PM
Points: 34, Visits: 129
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?


Post #126992
Posted Monday, July 19, 2004 5:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:58 PM
Points: 199, Visits: 410

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)




Post #127004
Posted Tuesday, July 20, 2004 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #127042
Posted Tuesday, July 20, 2004 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 7, 2012 2:15 PM
Points: 34, Visits: 129
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.


Post #127096
Posted Tuesday, July 20, 2004 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #127102
Posted Tuesday, July 20, 2004 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 7, 2012 2:15 PM
Points: 34, Visits: 129
Thanks for pointing me in the right direction.


Post #127111
Posted Tuesday, July 20, 2004 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:01 AM
Points: 6,954, Visits: 7,077

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 [table]




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

Anon.

Post #127163
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse