Dynamically alias columns from a view query SQL

Question

How can I use dynamic SQL to query a table, and then use one of the results to alias a column?

I'm trying something like:

SELECT 
  ID, ModelName INTO #tmpTable  
FROM Models

And then:

SELECT 
  ModelNumber AS (SELECT ModelName FROM #tmpTable)  
FROM NewModels

For those asking for more detail: We have a view that contains everything we want, but the columns are IDs like "def123". In another table we have the names that resolve the IDs like "def123", "FName". We want to query the view but have the name appear (using AS) instead of the ID. Essentially, we want to query the definitions table in the AS statement to get dynamic naming.

looks like you could do this off a simple join - whats the relationship between models and new models? — Daniel Marcus
– Daniel Marcus, Commented May 1, 2018 at 14:40
Unless #tmpTable only ever contains one unique value, I don't understand what you're looking for. Don't dumb down your problem, it makes it harder to solve, not easier. — Aaron Bertrand
– Aaron Bertrand, Commented May 1, 2018 at 15:04
A column can only have one name. So if you want a different name for each model as the column's name, that will not work. You can however build a string like 'SELECT ModelNumber AS [<what ever you like, insert here>] FROM NewModels' and have it executed by EXECUTE or sp_executesql. But what's the point? You could have done that without dynamic SQL too. — sticky bit
– sticky bit, Commented May 1, 2018 at 15:06

Random_User · Accepted Answer · 2018-05-01 15:15:01Z

1

Do not try to bend the dynamic SQL, only realize the truth of it, there is no need for it...

A ModelName by another other ModelNumber will still smell the same ...

#IMissSQLQuotes

select ID
     , ModelName as ModelNumber
  from NewModels

answered May 1, 2018 at 15:15

Random_User

3631 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Keith Fitts Over a year ago

I don't think this answers my question. I want to dynamically change the value after the as, based on another queries' results.

Random_User Over a year ago

As it is written this solves your issue, using my magic 8 ball I'm going to guess, that you want the data from one column to be the column name for a corresponding column of data. I will reference you to @larnu and the 4 up-voted comment. Please add a table definition and some sample data. Off the cuff it seems as though you'll be looking to use a pivot statement in addition to your dynamic SQL. Adding sample data and an illustration a lot of data ppl can figure out what you're looking for. When I have customers asking for this I typically show them other options as a result set.

LukStorms · Accepted Answer · 2018-05-01 19:18:05Z

One possible path to consider would be to replace alias names in a dynamic sql.
Based on a table with the new alias names.

Example snippet:

IF OBJECT_ID('tempdb..#NewModels') IS NOT NULL DROP TABLE #NewModels;
CREATE TABLE #NewModels (ModelNumber int, ModelType char(1));
INSERT INTO #NewModels (ModelNumber, ModelType) values 
(100, 'A'),
(101, 'B'),
(102, 'C');

IF OBJECT_ID('tempdb..#tmpModelNames') IS NOT NULL DROP TABLE #tmpModelNames;
CREATE TABLE #tmpModelNames (Code varchar(30) primary key, ModelName varchar(30));
INSERT INTO #tmpModelNames (Code, ModelName) values 
('Col1', 'Model Name 1'), 
('Col2', 'Model Name 2');

DECLARE @Sql NVARCHAR(max) = 'SELECT 
  ModelNumber AS [Col1], 
  ModelType AS [Col2]
FROM #NewModels
WHERE ModelNumber = @ModelNumber';

select @Sql = replace(@Sql, quotename(Code), quotename(ModelName)) from #tmpModelNames;
--select @Sql as Sql;

EXECUTE sp_executesql @Sql, N'@ModelNumber int', @ModelNumber = 101;

Returns:

Model Name 1  Model Name 2
------------  -------------
101           B

Collectives™ on Stack Overflow

Dynamically alias columns from a view query SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related