7

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
user2213892
  • 139
  • 1
  • 3
  • 10

2 Answers2

6

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"
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Thanks for the reply. The concept worked, but now I have 2 columns, one called 'ColumnName' and the second is 'ColumnName_1'. Is there anyway to change the 'ColumnName_1' to read something I specify? – user2213892 Apr 30 '13 at 22:05
1

you can use decode

select decode(ColumnName ,'A', 'Apple','B','Banana', 'Unknown') from ....
haki
  • 9,389
  • 15
  • 62
  • 110