CODEWITHSUNDEEP
sql-serverconditional-statements
Slind
Slind

Reputation: 13

SQL: how to make an if statement

I'm fairly new to MS SQL, so bear with me please.

I have a table with a bunch on info about customers, where I want to add columns and input values of either "yes" or "no" depending on the result of some values from the table.

Basically, I need something like this

SELECT customer_no, reg_no, @result_101, @result_301
      IF(reg_no*100)+other_number = 101
      THEN @result_101 = 'yes'
      ELSE @result_101 = 'no'

      IF(reg_no*100)+other_number = 301
      THEN @result_301 = 'yes'
      ELSE @result_301 = 'no' 
FROM customer_table

Is it possible for the customer to get 'yes' in both columns, as they have several "other_number"?

I can't really figure out how to work this out with the CASE statement - because it will just create double rows, so each customer will show up twice on the result set, like

customer_no      reg_no      @result_101     @result_301
01               0101         yes             no
01               0101         no              yes
02               0101         yes             no
02               0101         no              yes

and I need them to be gathered into one single row.

Upvotes: 0

Views: 73

Answers (2)

Guy Levin
Guy Levin

Reputation: 1258

You can use IIF for a simpler and cleaner solution:

Like this:

SELECT 

  customer_no, 
  reg_no, 
  IIF (reg_no * 100 + other_number = 101, 'yes', 'no') AS reg_101,
  IIF (reg_no * 100 + other_number = 301, 'yes', 'no') AS reg_301       

FROM customer_table

This will only work if you are using MS-SQL 2012 or higher

Upvotes: 1

jpw
jpw

Reputation: 44881

What you're looking for is the case expression:

SELECT 
  customer_no, reg_no, 
  result_101 = CASE 
                WHEN (reg_no*100)+other_number = 101 THEN 'yes'
                ELSE 'no'
               END,
  result_301 = CASE 
                WHEN (reg_no*100)+other_number = 301 THEN 'yes'
                ELSE 'no' 
               END
FROM customer_table

I removed the @ sign from your column names as @ is used for variables but if you really want it you can enclose it in brackets: [@result_101]

If you want to avoid duplicate rows you probably need to use some aggregate function with the case expression.

Upvotes: 1

Related Questions