CODEWITHSUNDEEP
sqlsql-server
Monika
Monika

Reputation: 61

If true do something else skip SQL

Hello, I have data that looks like this:

enter image description here

I'm trying to come with the code that will calculate the 'MERGE' column. Basically, I should check, if CLM_x >0 then take the value from SZ_x and concat with the value in CLM_x. I'm trying to use case when, however I don't know how to skip merging if CLM_x =0: CASE WHEN CLM_TBL1 > 0 THEN ('Size ' + SZ_1 + '-Qty '+CLM_1) else ... end ...

Please advise, Thank you!

Upvotes: 1

Views: 1091

Answers (2)

S3S
S3S

Reputation: 25132

You just need to string the case statements together.

merge = 
    case
        when CLM_1 > 0 then 'Size ' + SZ_1 + '-Qty '+ CLM_1 + ' '
        else ''
    end
    +
    case
        when CLM_2 > 0 then 'Size ' + SZ_2 + '-Qty '+ CLM_2 + ' '
        else ''
    end
    +
    case
        when CLM_3 > 0 then 'Size ' + SZ_3 + '-Qty '+ CLM_3 + ' '
        else ''
    end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Yuck. This is a bunch of string arithmetic:

select stuff( ((case when clm1_1 > 0 then concat(', Size ', sz_1, '-Qty ', clm1_1) else '' end) +
               (case when clm1_2 > 0 then concat(', Size ', sz_2, '-Qty ', clm1_2) else '' end) +
               (case when clm1_3 > 0 then concat(', Size ', sz_3, '-Qty ', clm1_3) else '' end)
              ), 1, 2, ''
             ) as merge_column

Upvotes: 5

Related Questions