Dynamic Aliases in the SQL statement

Question

I want to display Alias name based on the value of some other column name in the query in SQL Server. For e.g.

SELECT P.Amount AS (CASE P.Type WHEN 'Individual' THEN 'Salary' ELSE 'Profit' END)
  FROM Person P

I know the above is not right, but something like this will help.

Answer 1

I'm not sure if you can add dynamic aliases, but you should be able to do something like this (if you have only a few possible aliases):

SELECT
    CASE P.Type WHEN 'Individual' THEN P.Amount ELSE NULL END AS Salary,
    CASE P.Type WHEN 'Individual' THEN NULL ELSE P.Amount END AS Profit
FROM
    Person p

Answer 2

The "Alias" name is the name of the entire column of the data you are returning. It is not possible for this to change on a "by row" basis.

The only way you can change a column name (alias) dynamically is using dynamic SQL to build up your query. However, this does not appear to be what you are wanting to do.