CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Jason M
Jason M

Reputation: 1060

Dynamic Aliases in the SQL statement

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.

Upvotes: 9

Views: 18805

Answers (5)

mjv
mjv

Reputation: 75305

No can do...

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

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89741

A column can have one and only one name. If your rowset contained only one row, then you could look at the row's Type column first and then change the column name appropriately for the select. If it contains multiple 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 might need to re-examine your design.

Upvotes: 0

Luk&#225;š Lalinsk&#253;
Luk&#225;š Lalinsk&#253;

Reputation: 41316

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

Upvotes: 12

Robin Day
Robin Day

Reputation: 102548

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.

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147344

You'd need to return Amount as "Amount" and then return an extra column containing the "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

Upvotes: 1

Related Questions