dynamic column for years in a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

dynamic column for years in a query

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-07-30 : 12:38:55

How can i get the years as a column in my sql query...

Years
2011
2012
2013

if i user

select '2012' as year... I get only one value for it..How to accomadate the other values for it as shown above...

The years field is not present in any table but i want to get that as separate column from a TSql query for a drop down option in one of my report..Please advise...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 13:02:34

do you've a calendar table? if yes you can apply YEAR() function over datefield to get it

if you dont have any such table you need to either create one table using UDF like below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

or add a temporary table like below just for this query like


DECLARE @StartYear int

SET @StartYear=1998 -- just a value (you can set any value you want as start value)

;With YearValues (Yr)
AS
(
SELECT @StartYear
UNION ALL
SELECT Yr+1
FROM YearValues
WHERE Yr+1 <= YEAR(GETDATE())
)

SELECT Yr
FROM YearValues
ORDER BY Yr DESC

this will continue till current year and display it in descending which is how you usually show it on dropdowns

i prefer udf approach as its scalable and can even be made a permanent table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-07-30 : 16:23:49

thank you visakh ..i created a table on the server..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 17:09:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources