Variable Column Name - 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

General SQL Server Forums

New to SQL Server Programming

Variable Column Name

Author

Topic

aromao
Starting Member

3 Posts

Posted - 2006-08-11 : 09:33:38

Hi,

I have this query where I select data from the last 12 months (in 12 different columns).
What I would like to know is if it's possible to change the name of the columns to the month they refer to.

Thank's in advance,
AR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 09:40:25

quote:
Originally posted by aromao

Hi,

I have this query where I select data from the last 12 months (in 12 different columns).
What I would like to know is if it's possible to change the name of the columns to the month they refer to.

Thank's in advance,
AR

You have to explain more on what you are trying to do.

Post your table structure, some sample data and the expected result

aromao
Starting Member

3 Posts

Posted - 2006-08-11 : 09:48:12

Thank's,
Here is a sample of the query:

select pn.ref,pn.design,
'1'=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-12,getdate()))
and dbo.lastmonthday(dateadd(mm,-12,getdate()))
then pn.qtt else 0 end),

'2'=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-11,getdate()))
and dbo.lastmonthday(dateadd(mm,-11,getdate()))
then pn.qtt else 0 end) from pn;

What I would like is to have instead of '1' and '2', to have the correct month names.

Thanks in advance

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-11 : 10:05:01

Did u try the following?

select pn.ref,pn.design,
[January]=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-12,getdate()))
and dbo.lastmonthday(dateadd(mm,-12,getdate()))
then pn.qtt else 0 end),

[February]=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-11,getdate()))
and dbo.lastmonthday(dateadd(mm,-11,getdate()))
then pn.qtt else 0 end) from pn;

Srinika

aromao
Starting Member

3 Posts

Posted - 2006-08-11 : 10:09:06

The problem is I don't know the month because it will change every month. If getdate returns August then dateadd -12 will be August but if I run the query in September then dateadd -12 will be september.

Hope I explained it correctly.

Cheers,
AR

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-11 : 10:31:33

I think, u may need to use Dynamic SQL to achieve this.

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 10:36:54

you may not required dynamic sql.

Why not just use generic name as the column name like mth01, mth02


select pn.ref,pn.design,
mth01=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-12,getdate()))
and dbo.lastmonthday(dateadd(mm,-12,getdate()))
then pn.qtt else 0 end),

mth02=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-11,getdate()))
and dbo.lastmonthday(dateadd(mm,-11,getdate()))
then pn.qtt else 0 end) from pn;

and when you present the data in the front end change the name of the header accordingly.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-11 : 11:24:14

[code]select pn.ref,pn.design,
mth01=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-12,getdate()))
and dbo.lastmonthday(dateadd(mm,-12,getdate()))
then pn.qtt else 0 end) AS [January],

mth02=sum(case when fdata between
dbo.firstmonthday(dateadd(mm,-11,getdate()))
and dbo.lastmonthday(dateadd(mm,-11,getdate()))
then pn.qtt else 0 end) AS [February],
from pn;

[/code]

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources