Multiple THEN to a single CASE statement?

Question

I want to add an extra column. This extra column will get a value based on a case statment. My objective is for instance, although the syntax is very wrong, something to accomplish this: When 'A' then 'Apple' AND 'ExtraColumn'=1. I have tried to create an equvialnt to this using the code below, but I get an error that my SQL syntax is incorrect. ``` select Patient, case ColumnName when 'A' then 'Apple' when 'B' then 'Banana' end ColumnName, case ExtraColumn when 'A' then '1' when 'B' then '2' end ExtraColumn, ColumnResult from TABLE unpivot ( ColumnResult for ColumnName in (COL1,COL2,COL3) for ExtraColumn in (COL1,COL2,COL3) )u ```

Accepted Answer

You have to repeat your case construct for each column name. Something like this:

```
case ColumnName 
when 'A' then 'Apple'  
when 'B' then 'Banana'
end ColumnName,

case ColumnName
when 'A' then '1'  
when 'B' then '2'
end ExtraColumn,
```

There is a gotcha here. If you use ColumnName in your where clause, you might not like the results because you used it as an alias.

Edit starts here

You can make your aliases whatever you want. If they are simple, just type them.

```
 select column1 fred, column2 barney
```

If you want more than one word, or an sql keyword, use double quotes

```
select column1 "fred flinstone", column2 "select"
```

Multiple THEN to a single CASE statement?

Solution

Problem