469,126 Members | 1,276 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Adding a column in query

5
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?
Jan 16 '08 #1
2 1439
ck9663
2,878 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT field1, field2, andmoreotherfields, ...
  2. StateCodeDesc = 
  3. case when StateCode = '0' then 'Open'
  4. case when StateCode = '1' then 'Won'
  5. case when StateCode = '2' then 'Lost'
  6. else 'Undefined'
  7. end
  8. from yourTable ....
  9.  

-- CK
Jan 16 '08 #2
Gosi75
5
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT field1, field2, andmoreotherfields, ...
  2. StateCodeDesc = 
  3. case when StateCode = '0' then 'Open'
  4. case when StateCode = '1' then 'Won'
  5. case when StateCode = '2' then 'Lost'
  6. else 'Undefined'
  7. end
  8. from yourTable ....
  9.  

-- 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
Jan 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Tryfon Gavriel | last post: by
10 posts views Thread by Eric Petruzzelli | last post: by
4 posts views Thread by Tim Donaldson | last post: by
16 posts views Thread by Geoff Jones | last post: by
1 post views Thread by david.goodyear | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.