如何确定COALESCE运算符成功选择的列/值？

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

我有一个表，我希望从每个ID开始，从Col1开始，然后到Col2，再到Col3，从3（和3）列中找到第一个非空值

Note: Col3 is NEVER NULL

注意：Col3永远不会为空

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

要为每个值获取正确的列，请使用以下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

1 个解决方案

#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