Naming based on a subquery - 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

Naming based on a subquery

Author

Topic

dougiel
Starting Member

4 Posts

Posted - 2008-06-13 : 05:07:56

I'd like to name an output field based on the result of a subquery look up. This is the idea:

select colname1 as (select name from tableofnames where nameid=1)

..but that doesn't work. What would?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 05:18:09

select (select name from tableofnames where nameid=1) as colname1

Chirag

http://www.chirikworld.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 05:19:44

I think he means

select colname1 as (select name from tableofnames where nameid = 1)
from tavle1

and if the subquery returns 'Yak' the query would alias colname1 as Yak in the final resultset as

select colname1 as Yak ~~(select name from tableofnames where nameid = 1)~~
from tavle1

E 12°55'05.25"
N 56°04'39.16"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 05:24:57

if that would be the case.. then it can only be achieved using dynamic SQL..

Chirag

http://www.chirikworld.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 05:41:18

[code]DECLARE @SQL NVARCHAR(4000),
@ColumnName NVARCHAR(4000)

DECLARE @TableOfNames TABLE (NameID INT, Name NVARCHAR(20))

INSERT @TableOfNames
SELECT 1, 'Yak' UNION ALL
SELECT 2, 'Stupid idea'

SET @SQL = '
SELECT ColName1 AS ' + (SELECT QUOTENAME(Name) FROM @TableOfNames WHERE NameID = 1) + ',
ColName2 AS ' + (SELECT QUOTENAME(Name) FROM @TableOfNames WHERE NameID = 2) + '
FROM Table1'

PRINT @SQL[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources