SQL Server Case when syntax

Question

Ive a big select case in SQL query.

(select case tb_usuario.int_id_cargo 
                    when 13
                    then '20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53'
                    when 20
                    then ''
                    when 21
                    then '20'
                    when 22
                    then ''
                    when 23
                    then '22'
                    when 24
                    then '20,21,22,23'
                    when 25
                    then '20,21,22,23,24'
                    when 26
                    then '20,21,22,23,24,25'
                    when 27
                    then ''
                    when 28
                    then '27'
                    when 29
                    then ''
                    when 30
                    then '29'
                    when 31
                    then '27,28'
                    when 32
                    then '27,28,31'
                    when 33
                    then '29,30'
                    when 34
                    then '27,28,29,30,31,32,33'
                    when 35
                    then ''
                    else ''
                    end) as subs

If you pay attention you will realize there are many cases that return an empty string.I would like to check those that return empty string in a single case,i.e:

  (select case tb_usuario.int_id_cargo 
                        when 20,22,27,29,35
                                        then ''
                                         [...]

Is that possible?

Answer 1

How about using a table (a temporary one) to have this values selected without using a CASE statement?

migration (table name)
old_id      new_id
---------   --------------
24          20,21,22,23
30          29
.....

and your query will look like

select migration.new_id 
from migration inner join tb_usuario
on migration.old_id = tb_usuario.int_id_cargo

EDIT: For multiple old_id pointing to new_id with a blank string, you will have to create the entries separately

for e.g. (assuming that old_id 1 & 2 points to new_id -> blank)

migration (table name)
old_id      new_id
---------   --------------
1          
2

This will have an advantage of changing the new_id from blank to something else using this table than to change the query written above.

Answer 2

select (case 
       when tbl_usuario.int_id_cargo in (20,22,27,29,35,.....) then '' 
       else
            case tbl_usuario.int_id_cargo
            when 23 then '22'
            when 25 then '20,21,22,23,24'
            when ....
            end
        end) as subs

replace .... with the rest of the ids and then fix the second case statement so that matches up for all the other values

Answer 3

I would just leave it to the ELSE clause. Only name the IDs you wanted to match up in the CASE statement. Unless you have a reason to keep those IDs in the case statement.

(select case tb_usuario.int_id_cargo 
                    when 13
                    then '20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53'
                    when 21
                    then '20'
                    when 23
                    then '22'
                    when 24
                    then '20,21,22,23'
                    when 25
                    then '20,21,22,23,24'
                    when 26
                    then '20,21,22,23,24,25'
                    when 28
                    then '27'
                    when 30
                    then '29'
                    when 31
                    then '27,28'
                    when 32
                    then '27,28,31'
                    when 33
                    then '29,30'
                    when 34
                    then '27,28,29,30,31,32,33'
                    else ''
                    end) as subs

Answer 4

Use:

(SELECT CASE 
         WHEN tb_usuario.int_id_cargo = 13 THEN
          '20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53'
         WHEN tb_usuario.int_id_cargo IN(20, 22, 27, 29, 35) THEN
            ''
         WHEN tb_usuario.int_id_cargo = 21 THEN
            '20'
         WHEN tb_usuario.int_id_cargo = 23 THEN
            '22'
         WHEN tb_usuario.int_id_cargo = 24 THEN
            '20,21,22,23'
         WHEN tb_usuario.int_id_cargo = 25 THEN
            '20,21,22,23,24'
         WHEN tb_usuario.int_id_cargo = 26 THEN
            '20,21,22,23,24,25'
         WHEN tb_usuario.int_id_cargo = 28 THEN
            '27'
         WHEN tb_usuario.int_id_cargo = 30 THEN
            '29'
         WHEN tb_usuario.int_id_cargo = 31 THEN
            '27,28'
         WHEN tb_usuario.int_id_cargo = 32 THEN
            '27,28,31'
         WHEN tb_usuario.int_id_cargo = 33 THEN
           '29,30'
         WHEN tb_usuario.int_id_cargo = 34 THEN
           '27,28,29,30,31,32,33'
         ELSE ''
        END) as subs

Though I don't see the point in distinguishing the specific values that should return a zero length string vs just letting the ELSE (which returns the same value) catch these:

(SELECT CASE tb_usuario.int_id_cargo 
         WHEN 13 THEN
           '20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53'
         WHEN 21 THEN
            '20'
         WHEN 23 THEN
            '22'
         WHEN 24 THEN
            '20,21,22,23'
         WHEN 25 THEN
            '20,21,22,23,24'
         WHEN 26 THEN
            '20,21,22,23,24,25'
         WHEN 28 THEN
            '27'
         WHEN 30 THEN
            '29'
         WHEN 31 THEN
            '27,28'
         WHEN 32 THEN
            '27,28,31'
         WHEN 33 THEN
           '29,30'
         WHEN 34 THEN
           '27,28,29,30,31,32,33'
         ELSE ''
        END) as subs