How to figure out which column/value the COALESCE operator successfully selected?

Question

I have a table that I wish to find the first non-null value from 3 (and only 3) columns for each ID starting with Col1 then to Col2 then to Col3

Note: Col3 is NEVER NULL

ID    Col1    Col2    Col3
------------------------------
1     A       B       X
2     NULL    C       X
3     NULL    NULL    X
4     D       NULL    X

To get the correct column for each value I use the following SQL Select

SELECT    ID,
          COALESCE(Col1, Col2, Col3) AS Col
FROM      MyTable

which returns the following and works just fine

ID    Col
-------------
1     A
2     C
3     X
4     D

What I want is a third column returned indicating which column the coalesce was successful on. The following is the result set that I wish to produce:

ID    Col    Source
-----------------------
1     A      Col1
2     C      Col2
3     X      Col3
4     D      Col1

Answer 1

Perhaps this will work?

SELECT    ID,
          COALESCE(Col1, Col2, Col3) AS Col,
          CASE COALESCE(Col1, Col2, Col3)
              WHEN Col1 THEN 'Col1'
              WHEN Col2 THEN 'Col2'
              WHEN Col3 THEN 'Col3'
              ELSE 'Unknown'
          END AS Source
FROM      MyTable