[ACCEPTED]-Dynamic Aliases in the SQL statement-tsql

Accepted answer

Score: 12

I'm not sure if you can add dynamic aliases, but 2 you should be able to do something like 1 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

Score: 2

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

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

Score: 1

You'd need to return Amount as "Amount" and 2 then return an extra column containing the 1 "type" for that amount.

i.e.g

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

Score: 1

No can do...

SQL returns a recorset which 13 can only have one name / alias per each column.
Which name would it choose it example 12 given if some records returned by the query 11 were 'Individual' and some were some other 10 type?

Of course, as suggested in several 9 responses, you can modify the number of 8 columns returned by the query and name each 7 column as desired but dealing with such 6 a results set that may then require additional 5 logic, which would defeat the purpose since 4 if one wanted extra logic, he/she may just 3 select both the Amount and the Type and 2 work off these values for attribute naming 1 and such at the level of the application...

Score: 0

A column can have one and only one name. If 6 your rowset contained only one row, then 5 you could look at the row's Type column 4 first and then change the column name appropriately 3 for the select. If it contains multiple 2 rows, it's simply not possible.

IF 1 = (SELECT COUNT(*) FROM Person P WHERE <where-criteria>) THEN
    IF 'Individual' = (SELECT P.Type FROM Person P WHERE <where-criteria>) THEN
        SELECT P.Amount AS Salary
        FROM Person P
        WHERE <where-criteria>
    ELSE
        SELECT P.Amount AS Profit
        FROM Person P
        WHERE <where-criteria>
    END IF
ELSE
    SELECT P.Amount AS SalaryOrProfit
    FROM Person P
    WHERE <where-criteria>
END IF

I think you 1 might need to re-examine your design.

Source: stackoverflow.com

[ACCEPTED]-Dynamic Aliases in the SQL statement-tsql

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

SQL Server 2005 Get First and Last date for any Month in any Year

How to create sequential number column index on table with data

retrieve the most recent record for each customer

Why can't I perform an aggregate function on an expression containing an aggregate but I can do so by creating a new select statement around it?

MS SQL Server: Check to see if a user can execute a stored procedure

Which is better: Bookmark/Key Lookup or Index Scan

Create a global static variable in SQL Server?

T-SQL User defined function overloading?