• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

SQL dynamic column names

Hi,

I have tried to simplify as much as possible..

I have this T-sql:

declare @year int
set @year = 2011

Select 
	January as [b]'xx'[/b]
from 
	dbo.ReportInternalLines R 
	INNER JOIN dbo.ReportInternalTypes RT ON R.ReportInternalType_Id = RT.Id 
where 
	[Year] = @year
	and RT.Keyvalue = 300 

Open in new window


Now this code works, but I want to set the column name to 'Income 2011', where the '2011' should be taken from the @year parameter.
The reason for this is, because i need to have columns for the last 5 years.. @year-1..5..

Plz help ??

0
Srm74
Asked:
Srm74
2 Solutions
 
DerZaubererCommented:
you can use exec[ute] to run a command string:

exec('Select January as Income' + @year + ' from dbo.ReportInternalLines R INNER JOIN dbo.ReportInternalTypes RT ON R.ReportInternalType_Id = RT.Id where [Year] = ' + @year + ' and RT.Keyvalue = 300')
0
 
mcs0506Commented:
Hi
This is possible with the help dynamic query execution so this first create dynamic query then execute it.
Here is the sample

DECLARE @year INT
DECLARE @y NVARCHAR(20)
DECLARE      @queryStr NVARCHAR(max)
SET @year = 2011
SET @queryStr= 'SELECT January AS Income' + @year + ' FROM dbo.ReportInternalLines R INNER JOIN dbo.ReportInternalTypes RT ON R.ReportInternalType_Id = RT.Id where [Year] = ' + @year + ' AND RT.Keyvalue = 300'
EXEC (@queryStr)

--OR
--EXECUTE      (@queryStr)


Regards

Dani
0
 
Srm74Author Commented:
Thx... works for me.. :-)
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now