Casing column alias

Question

Hi,

I have

m.MerchantIdentifier as 'Merchant Identifier',

I am wanting something like

case when salesoffice = 'Pia' then 
m.MerchantIdentifier 
end as  'PiaJewlerry'
else end as 'Merchant Identifier'

How could I do this in T-SQL

Answer 1

Hi @Christopher Jack ,

If you have only one row of result, you could try with dynamic way like below:

declare @sql nvarchar(max)  
  
set @sql= 'select MerchantIdentifier as '  
+ case when (select salesoffice from yourtable) = '''Pia''' then 'PiaJewlerry' else   
 '''Merchant Identifier''' end + ' from yourtable'  
  
 EXECUTE sp_executesql  @sql  

If you have more than one row, you could not alias the column name in that way as mentioned by other experts.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can not, all object and aliase names have to be fix defined. What should that be good for and how should the client know which alias is defined for which source column?

Only way would be dynamic SQL; but better don't use it.
https://www.sommarskog.se/dynamic_sql.html

Answer 3

As @Olaf Helper mentioned you don't want to be changing the column name in the middle of a query. Remember you are generally returning rows of data back so each column you specified is going to be returned irrelevant of the contents of the row.

If you need values returned in multiple columns then you're going to need to create an expression for both columns. Just a rough thought here:

   case when salesoffice = 'Pia' then   
    m.MerchantIdentifier   
    end as  'PiaJewlerry'  
    case when salesoffice <> 'Pia' then  
    m.MerchantIdentifier  
    end as 'Merchant Identifier'  

When the sales office is Pia then the PiaJewlerry column is the merchant ID and the Merchant Identifier column is NULL.
When the sales office is not Pia then the PiaJewlerry column is NULL and the Merchant Identifier column is the merchant ID.

I believe this is what you were trying for anyway.

Answer 4

Permit me to point out that what you ask for does not really make sense. There are many values in a column, so do you want different aliases for different rows? That does not really fly.

What you can do if you want dynamic column names is to insert the data into a temp table and then use sp_rename to rename the columns as desired.

But keep in mind that this is largely a presentational issue, so it is better do handles this in the client.

Answer 5

I believe what you are actually asking for is this:

 case when salesoffice = 'Pia' then 'PiaJewlerry' else m.MerchantIdentifier  end as 'Merchant Identifier'