Getting column name instead of column value

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

SQL Server Development (2000)

Getting column name instead of column value

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-04-09 : 19:29:21

Hiya!
I'm returning a resultset which shows aging of balances. The SELECT uses CASE statements and returns 5 columns showing aging amounts for each range: 0-29 days, 30-59 days, etc. I'd also like a column with the oldest range. E.g. if someone has $10 in 0-29 and $20 in 30-59, that column should have a 59. This column will be displayed in a report field which will say "Your account is N days past due...". The column may be character or numeric data. The Coalesce function, when passed all 5 range columns in backwards order (like this: Coalesce(120+,90-119,60-89,30-59,0-29))does the proper thing here, but it will return the column's monetary value, not the column name.

Sarah Berger MCSD

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-09 : 23:34:21

I'm afraid there's nothing pretty and you'll just have to do

select
oldestrange = 'Your oldest range is ...' +
case when range2 is not null then 'range2'
when range1 is not null then 'range1'
when rangeO is not null then 'range0' end
+ ' days and the amount is ' + CAST(coalesce(range2,range1,rangeO) as nvarchar(50))
from table

(I've used range1, range2 rather than "120+" etc)

here's my example code


create table #a (id int identity(1,1), rangeO int , range1 int , range2 int)

insert into #a (rangeO, range1, range2) select NULL,10,200
insert into #a (rangeO, range1, range2) select 7,3,NULL
insert into #a (rangeO, range1, range2) select 90,1,111
insert into #a (rangeO, range1, range2) select NULL,NULL,45
insert into #a (rangeO, range1, range2) select 100,NULL,NULL
insert into #a (rangeO, range1, range2) select 120,10,50


select * from #a

select id, 
oldestrange = 'Your oldest range is ...' + 
	case when range2 is not null then 'range2' 
	when range1 is not null then 'range1'
	when rangeO is not null then 'range0' end
	+ ' days and the amount is ' + CAST(coalesce(range2,range1,rangeO) as nvarchar(50))

from #a

drop table #a

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-09 : 23:36:50

Of course if you're ranges are going to keep changing and you don't want them hardcoded, then you'll need to write some dynamic SQL

HTH

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources