How to combine specific column values in an SQL statement

Question

There are two variables in our database called OB and B2B_OB. There are EP codes and each EP may have one of the following 4 combinations:

 EP  OB B2B_OB
---  -- ------
 3   X
 7        X
11  
14   X    X

What I want to do is to combine the X's in the fetched list so that I can have A or B value for a single EP. My conditions are:

IF (OB IS NULL AND B2B_OB IS NULL) THEN TYPE = A  
IF (OB IS NOT NULL OR B2B_OB IS NOT NULL) THEN TYPE = B

The list I want to fetch is like this one:

 EP  TYPE
---  ----
 3   B
 7   B
11   A
14   B

How can I do this in my query? TIA.

Unreason · Accepted Answer · 2010-04-16 07:57:09Z

4

(EDIT: corrected AND to OR)
Here's a solution (syntax tested on postgres, but from what I read it should be the same)

select ep, 
  case
    when ob is null and b2b_ob is null then a
    when ob is not null or b2b_ob is not null then b
    else null
  end as type
from table;

Note: else null is redundant but only wanted to emphasize the default else case.

edited Apr 16, 2010 at 7:57

answered Apr 15, 2010 at 8:29

Unreason

12.7k2 gold badges39 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AndyDan Over a year ago

Looks like the second line of the CASE should be when ob is not null OR b2b_ob is not null then b

Collectives™ on Stack Overflow

How to combine specific column values in an SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related