SQL Variable Column Name depends on Row data

Question

I am tryin to get a column from DB that returns Variable Column Name which depends on Row data. I know I can have variable Column name with using Dynamic SQL, but what if the name actually depends on the row's information.

SELECT name,age FROM dbo.Names                           

--Reurns 'name' as column name

SELECT name as [xyz],age FROM dbo.Names                 

--Returns 'xyz' as column name

EXEC 'SELECT name as [' +  @var + '], age FROM dbo.Names'

--Returns @var value as Column name

SELECT name AS ['Hi: ' + age ] FROM dbo.Name ?????       

--So I am trying to get 'Hi: 25' or 'Hi: 40' as column name

How would I do that? Any help please?

Answer 1

You can combine the approaches for one line, but not for selecting all rows:

DECLARE @age INT
SET @age = SELECT TOP 1 @age FROM dbo.NAMES
EXEC 'SELECT name as [Hi ' +  @age + '], age FROM dbo.Names'

Why do you need to do this in SQL and not in application logic?

Answer 2

I am gonna have to change on Application Side. I don't think that's possible to change in the SQL Side. That logic only works for the 1 row, I need multiple rows.