• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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