SQL Server : conditional concatenation

debugcn Published at Dev

mark1234

I'm sure there's a better way to do this (SQL Server 2014).

I have 3 columns that represent approval by customer, sales or plant. The values will be 'Yes' or 'No' (3rd party app). I need to abbreviate the selected 'Yes' fields to a separated list of the first initials; for example if Sales and Client are 'Yes' I need to show 'S/C' on a report.

I've done it like this for now, as it was time sensitive, but I'm sure there's a better way:

case
   when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue = 'Yes' 
      then 'P/S/C'
   when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue  = 'No' 
      then 'P/S'
   when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue  = 'Yes' 
      then 'P/C'
   when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue  = 'Yes' 
      then 'S/C'
   when cfv1.CVFieldValue = 'Yes' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue  = 'No' 
      then 'P'
   when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'Yes' and cfv3.CVFieldValue  = 'No' 
      then 'S'
   when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue  = 'Yes' 
      then 'C'
   when cfv1.CVFieldValue = 'No' and cfv2.CVFieldValue = 'No' and cfv3.CVFieldValue  = 'No' 
      then ''
end as OKBy

Anyone got a better way?

TIA

Mark

Bogdan Sahlean

1) First solution: you could use a mapping table

SELECT  x.*, s.RetValue
FROM    dbo.SomeTable x
LEFT JOIN
(   -- You could insert bellow values into a temp table / @table variable
    -- Warning: following pair of values (CVFieldValue, CVFieldValue2, CVFieldValue3) should be UNIQUE
    VALUES 
    ('Yes', 'Yes', 'Yes', 'P/S/C'),
    ('Yes', 'Yes', 'No', 'P/S'),
    ('Yes', 'No', 'Yes', 'P/C') --, ...
) map (CVFieldValue1, CVFieldValue2, CVFieldValue3, RetValue)
ON  x.CVFieldValue1 = s.CVFieldValue1
AND x.CVFieldValue2 = s.CVFieldValue2
AND x.CVFieldValue3 = s.CVFieldValue3

2) Second solution: IIF and CONCAT (SQL2012+)

SELECT  STUFF(
            CONCAT(
                IIF(CVFieldValue1 = 'Yes', '/P', ''),
                IIF(CVFieldValue2 = 'Yes', '/S', ''),
                IIF(CVFieldValue3 = 'Yes', '/C', ''),
                ' ' -- If you remove this line then result will be NULL when all columns have non 'Yes' values
            ),
            1, 1, '') AS Result
    ,*
FROM    (
    VALUES 
    ('Yes', 'Yes', 'Yes'),
    ('Yes', 'Yes', 'No'),
    ('Yes', 'No', 'Yes'), -- ... Source table
    ('No', 'No', 'No')
) SomeTable (CVFieldValue1, CVFieldValue2, CVFieldValue3)

Collected from the Internet

Please contact [email protected] to delete if infringement.