Changing Column Names from a lookup table

Question

Changing Column Names from a lookup table

pjr001

SSC-Addicted

Points: 466
More actions
October 7, 2010 at 2:33 am

#230087

Hi
I have 2 tables, dispLookup and campaign.
The dispLookup contains 2 columns, Code and Disp.
The campaign table contains date, time, code000...code199.
The dispLookup table contains a nice name for each column in the campaign table, e.g. code000 when you look it up in the dispLookup table (via Code column) is 'Time on Call'.
The dispLookup table contains NULL in Disp column if that code is not required.
So what I would like to be able to do is select columns from the campaign table but instead of showing the code000...code199 as column names, I would like to lookup the dispLookup table and show the nice names, also if a code is NULL in dispLookup not display that column at all.
any help gratefully received
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

hallidayd SSCrazy Points: 2490 More actions · Answer 1

pjr001 (10/7/2010)
Hi
I have 2 tables, dispLookup and campaign.
The dispLookup contains 2 columns, Code and Disp.
The campaign table contains date, time, code000...code199.
The dispLookup table contains a nice name for each column in the campaign table, e.g. code000 when you look it up in the dispLookup table (via Code column) is 'Time on Call'.
The dispLookup table contains NULL in Disp column if that code is not required.
So what I would like to be able to do is select columns from the campaign table but instead of showing the code000...code199 as column names, I would like to lookup the dispLookup table and show the nice names, also if a code is NULL in dispLookup not display that column at all.
any help gratefully received
Thank you

You cannot do this without Dynamic SQL. I am afraid that this design seems, on the face of it, extremely poor and it is difficult to imagine any advantages it could bring. What reason did you have for giving your columns generic names and creating a table mapping these to meaningful names, instead of just naming the columns meaningfully in the first place?

Personally, my advice would be to write some one off dynamic SQL to rename all the columns in your table to the meaningful values in dispLookup and then drop dispLookup.

tommyh SSCertifiable Points: 6302 More actions · Answer 2

Have to agree with that. Design is horrible. However if thats not possible

--Some tables and data to test against.. since i dont have this

create table dispLookup (Code int, Disp varchar(256) primary key (Code))

create table Campaign ([Date] varchar(32), [Time] varchar(32), code000 varchar(32), code001 varchar(32), code002 varchar(32))

insert into dispLookup (Code, Disp) values (0, 'Time on Call')

insert into dispLookup (Code, Disp) values (2, 'Something')

--End testcode

--The code your interested in

declare @i int

declare @sql varchar(max)

select @i = -1, @sql = ''

while exists (select * from dispLookup where Code > @i)

begin

select top 1 @sql = @sql + ', Code' + Right('00' +Convert(varchar(3), Code), 3) + ' as [' + Disp + ']', @i = Code

from dispLookup

where Code > @i

and Disp is not null

order by Code

end

select @sql = 'select [Date], [Time], ' + Stuff(@sql, 1, 2, '') + ' from Campaign'

exec (@sql)

--End your code

--Cleaning my testcode up

drop table dispLookup

drop table Campaign

I have offcourse have had to guess the datatypes. But should work okayis for you maybe with some modifications.

The SQL code that gets executed (by the Exec) looks like this

select [Date], [Time], Code000 as [Time on Call], Code002 as [Something] from Campaign

There is offcourse the risk off an infinite loop depending on how the tables actually look. But this should get you started.

/T

pjr001 SSC-Addicted Points: 466 More actions · Answer 3

These tables have been provided for me and I have no ability to change the design I'm afraid.

I can query the system but cannot change the underlying design.

I think the reason they do it this way is because the tables are populated by a dialler automatically and rather than create a new table dynamically for each dialler campaign, they use the same table but include a campaign name and allow you to assign friendly names to each column via a lookup table, seems ressonable to me.

I will look into dynamic sql, at least I know it can't be done using standard T-SQL anyway.

Thank you

pjr001 SSC-Addicted Points: 466 More actions · Answer 4

tommyh (10/7/2010)
Have to agree with that. Design is horrible. However if thats not possible
--Some tables and data to test against.. since i dont have this
create table dispLookup (Code int, Disp varchar(256) primary key (Code))
create table Campaign ([Date] varchar(32), [Time] varchar(32), code000 varchar(32), code001 varchar(32), code002 varchar(32))
insert into dispLookup (Code, Disp) values (0, 'Time on Call')
insert into dispLookup (Code, Disp) values (2, 'Something')
--End testcode
--The code your interested in
declare @i int
declare @sql varchar(max)
select @i = -1, @sql = ''
while exists (select * from dispLookup where Code > @i)
begin
select top 1 @sql = @sql + ', Code' + Right('00' +Convert(varchar(3), Code), 3) + ' as [' + Disp + ']', @i = Code
from dispLookup
where Code > @i
and Disp is not null
order by Code
end
select @sql = 'select [Date], [Time], ' + Stuff(@sql, 1, 2, '') + ' from Campaign'
exec (@sql)
--End your code
--Cleaning my testcode up
drop table dispLookup
drop table Campaign
I have offcourse have had to guess the datatypes. But should work okayis for you maybe with some modifications.
The SQL code that gets executed (by the Exec) looks like this
select [Date], [Time], Code000 as [Time on Call], Code002 as [Something] from Campaign
There is offcourse the risk off an infinite loop depending on how the tables actually look. But this should get you started.
/T

🙂 Thank you very much, that's exactly the start I was looking for 🙂

drew.allen SSC Guru Points: 76998 More actions · Answer 5

Assuming that your dispLookup table changes relatively infrequently, it might be best to create a trigger on dispLookup to dynamically create/alter a view with the user-friendly column names. This seems like a relatively safe assumption to me based on what little I know of the database

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pjr001 SSC-Addicted Points: 466 More actions · Answer 6

drew.allen (10/7/2010)
Assuming that your dispLookup table changes relatively infrequently, it might be best to create a trigger on dispLookup to dynamically create/alter a view with the user-friendly column names. This seems like a relatively safe assumption to me based on what little I know of the database
Drew

Thank you, I didn't think of doing it that way, although, the solution from SSC Eights! has provided me with the solution I was looking for.