Can I dynamically assign a column alias.

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

Can I dynamically assign a column alias.

Author

Topic

Bluespud
Starting Member

5 Posts

Posted - 2010-08-13 : 06:44:20

Hi,
Hi,

I am trying top create a view where I want a particular column name to be based on database content rather than a literal. I have donre an example of what I want to do below, but this is not allowed. Can enyone tell me if this can be done, and if so, how do I do it. Thanks in advance.

Occupation
===========
ID NAME
-- ----
01 Builder
02 Teacher

User
=======
ID NAME OCCUPATION_ID
-- -------- -------------
01 Fred 02
02 Jim 02
03 Tom 01
04 Pat 02

create view MyView
as
select u.id, u.name, o.name as (select name from occupation where id = 02)
from user u, occupation o
where u.occupation_id = o.id
and u.occupation_id = 02;

to give me a view MyView as follows:

ID NAME TEACHER
-- ---- -------

Here, the column name TEACHER is based on database content, not a literal.

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-13 : 06:52:50

Hello,

You can try this:

DECLARE @SQL varchar(max)
SET @SQL='create view MyView as select u.id, u.name, o.name as'
SET @SQL=@SQL+' '+(SELECT name FROM occupation WHERE id=2)
SET @SQL=@SQL+' FROM user u, occupation o WHERE u.occupation_id = o.id AND u.occupation_id = 2;'

EXEC (@SQL)

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-13 : 07:55:00

How about something like this, and you can just select from the view with whatever parameters you want.


DECLARE @Name TABLE (ID tinyint identity(1,1),aName varchar(10))
DECLARE @User TABLE (ID tinyint identity(1,1),aName varchar(10),Occupation_ID tinyint)

INSERT INTO @Name
SELECT  'Builder' UNION SELECT 'Teacher'

INSERT INTO @User
SELECT 'Fred',2 UNION ALL
SELECT 'Jim',2 UNION ALL
SELECT 'Tom',1 UNION ALL
SELECT 'Pat',2

DECLARE @parameter varchar(10)
SET @parameter = 'Builder'


SELECT u.id,u.aName
     ,COALESCE(CASE WHEN n.aName = 'Teacher' THEN n.aName END 
     ,CASE WHEN n.aName = 'Builder' THEN n.aName END)  as Occupation
FROM @name n
INNER JOIN @user u on  n.id = u.occupation_id

Jim

Everyday I learn something that somebody else already knew

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-14 : 04:55:07

search in forum. this issue has be solved couple of times.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources