Dynamicaly Changing Col names - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Dynamicaly Changing Col names

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2008-11-18 : 06:54:36

Hi all

I have a query that calculates a count of open cases based on two variables as follows.

DECLARE @FirstMonth AS INT
SET @FirstMonth = 1

DECLARE @SecondMonth AS INT
SET @SecondMonth = 2

SELECT
cm.MappingOrder,
cm.MappingDescriptor,
COUNT(CASE WHEN DATEDIFF(mm,m.[DATE-OPENED],GETDATE()) >= @FirstMonth AND DATEDIFF(mm,m.[DATE-OPENED],GETDATE()) <= @FirstMonth THEN 1 END) As October,
COUNT(CASE WHEN DATEDIFF(mm,m.[DATE-OPENED],GETDATE()) >= @SecondMonth AND DATEDIFF(mm,m.[DATE-OPENED],GETDATE()) <= @SecondMonth THEN 1 END) As November,
COUNT(m.[DATE-OPENED]) [Total Active]

FROM
SOLCASEDW.dbo.MATDB m,
SOLCASEDW.dbo.SUPA_CAD_Case_Details sccd,
Lawsoft.dbo.CasetypeMappings cm
WHERE
m.[MT-CODE]=sccd.Matter_Code_MT05_txt AND
sccd.CAD08_CSA_Case_Type*=cm.SystemDescriptor AND
m.[CL-CODE]='00000291' AND
m.[MSTONE-CODE] <> 'COMP'
GROUP BY
cm.MappingOrder,
cm.MappingDescriptor
ORDER BY
cm.MappingOrder

i would like to dynamicaly put the month names in as the colum headers, so when it comes to running this next month the headers are worked out automaticaly. can this be done?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-18 : 07:41:58

Put your query in a string variable and execute it. like

DECLARE @QueryText VARCHAR(200)
DECLARE @columnheader VARCHAR(100)
SELECT @QueryText = 'SELECT yourcolumn AS '+@columnheader+' FROM yourtable'

EXEC (@QueryText)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-18 : 08:03:23

Don't do this!
Dynamically changing column names is a BAD idea.
How is any reporting tool or user interface going to know what the output schema of your procedure is going to be?
Just output the data as months 0, 1, 2, etc. Your application should know what parameter was passed to indicate the first month, or you can output the first month as a column.
Then, let your application layer handle data presentation. That is what it is for!

If it is not practically useful, then it is practically useless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources