Adding a column in query

[Gosi75]

I am working on Microsoft SQL Server Reporting Services(SSRS) and need to create dataset that shows 2 columns, one with values from the table StateCode in the database and the other column should show the meaning of each value in the StateCode table.

The StateCode table includes 3 values, 0,1,2 where:

• 0 = Open
• 1 = Won
• 2 = Lost

My problem here is, that no column showing the meaning of StateCode values exists for those 3 values(Open,Won,Lost). Therefore I need to add that to my query, that is f.exs., when StateCode '0' is returned, 'Open' appears also in that row.

Is there any way to do that in the query?

[ck9663]

I am working on Microsoft SQL Server Reporting Services(SSRS) and need to create dataset that shows 2 columns, one with values from the table StateCode in the database and the other column should show the meaning of each value in the StateCode table.

The StateCode table includes 3 values, 0,1,2 where:

• 0 = Open
• 1 = Won
• 2 = Lost

My problem here is, that no column showing the meaning of StateCode values exists for those 3 values(Open,Won,Lost). Therefore I need to add that to my query, that is f.exs., when StateCode '0' is returned, 'Open' appears also in that row.

Is there any way to do that in the query?

if your list becomes longer and becomes more dynamic i would recommend you use a table...if this is that short list and more of static thing...

try:

SELECT field1, field2, andmoreotherfields, ...
StateCodeDesc = 
case when StateCode = '0' then 'Open'
case when StateCode = '1' then 'Won'
case when StateCode = '2' then 'Lost'
else 'Undefined'
end
from yourTable ....
 

-- CK

[Gosi75]

if your list becomes longer and becomes more dynamic i would recommend you use a table...if this is that short list and more of static thing...

try:

SELECT field1, field2, andmoreotherfields, ...
StateCodeDesc = 
case when StateCode = '0' then 'Open'
case when StateCode = '1' then 'Won'
case when StateCode = '2' then 'Lost'
else 'Undefined'
end
from yourTable ....
 

-- CK

Thanks for the help, it helped me a lot. However did that not work for for me, so I tried this :

SELECT StateCode, StateCodeName =
Case StateCode
When '0' then 'Open'
When '1' then 'Won'
When '2' then 'Lost'
else 'Undefined'
end
from MyTableName