question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked MelissaMa-msft commented

Casing column alias

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






sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Is this a single-row result?


0 Votes 0 ·

Hi @ChristopherJack-1763,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @ChristopherJack-1763,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

I believe what you are actually asking for is this:

  case when salesoffice = 'Pia' then 'PiaJewlerry' else m.MerchantIdentifier  end as 'Merchant Identifier'
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
1 Vote"
cooldadtx answered

As @OlafHelper-2800 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.