Search code examples
sqlsql-server-2008coalesce

COALESCE function and to get the data from the supporting field

The subject seems confusing like I am confused on how to resolve the problem in hand. I have a table TESTCODES with data

testcode1   testdesc1   testcode2   testdesc2   testcode3   testdesc3
SB00001         CBC         1234-1          CBC Panel   NULL            NULL
NULL            NULL            1234-4          XRAY            NULL            NULL
SB00002     'Culture B'     1234-2          Blood Cltr  989889          Blood Cnt

I have a query that gets me the TestCode from these fields:

SELECT COALESCE(TestCode1, TestCode2, TestCode3, '') TestCode FROM SOMEORDER

The coalesce works fine and returns the data as expected. But, I want the result to show which test code did it select. Eg:

TestCode
SB00001 - TestCode1
1234-4  - TestCode2
SB00002 - TestCode1

Can I do it with COALESCE itself or I need to change this query to use CASE statements?

Solution

  • It looks like you want to concatenate the field name to the actual value. You can do this inside the coalesce():

    SELECT COALESCE(TestCode1 + ' - TestCode1',
                TestCode2 + ' - TestCode2',
                TestCode3 + ' - TestCode3',
                ''
               ) TestCode
FROM SOMEORDER;

    The values that are NULL will still be NULL after the concatenation.