Column Alias in Case When

Transact-SQL
1

I need to be able to change the column alias returned in a Select depending on a column value, so something like...

		  CASE 
			 WHEN [rTYPE] = 'Risk' THEN rReference as 'Risk Reference'
			 ELSE rReference as 'Issue Reference'
		  END

So it would return the value of rReference in a column called either "Risk Reference" or "Issue Reference"

is anything like this possible - or what would be the better way of doing this?

Hope this makes sense?

Thanks, Phil

2

That's not possible. How would would it handle the situation where one of each is returned? What would the column name be in this case?

3

Yeah, I assumed it wouldn't be possible, but I wondered if there was a way to do this without writing two different selects.

Phil

4

This is not even logical. Values can Risk for some of the rows and other than Risk for others, then how can you give different column aliases at the same time.

5

return as 2 separate column ?

CASE WHEN [rTYPE] =  'Risk'  THEN rReference END as 'Risk Reference' ,
CASE WHEN [rTYPE] <> 'Risk'  THEN rReference END as 'Issue Reference'
6

Your problem is that you do not understand what a table is. "What you want is "Automobiles, squids and Lady Gaga" instead of a table. The table is made up of roads; rows are made up of columns. The model scaler values. Each row has one and only one structure; a column does not change from an automobile to a squid depending on some outside influence like your R type. I also worry about the phrase are type; have you ever heard of ISO 11179 naming standards? Is quote our quote a known kind of thing in your trade, or is it metadata that should have no place in a data element name? Should risk and issue be types in a type column instead of trying to change the structure of the table on the fly row by row?