sql - 如何在 SQL Server 中跨多列选择所有非数字值？

Question

我有一张表价格

ID Price_1 Price_2 Price_3

P1 10      11      12
P2 13      14      15
P3 aa      16      bb
P4 19      cc      20

---

从上面可以看出，Price_1、Price_2 和 Price_3 列中的某些值可能不是数字。

我想要的是首先找到所有那些非数字值然后给出一个总结(连接所有非数字值和一个id的列)

所以对于上面的例子，我想要的是

ID   Bad_Columns      Bad_Values
P3   Price_1,Price_3  aa,bb
P4   Price_2          cc

---

我应该如何写这个查询？

Answer 1

你可以这样做:

WITH CTE AS
(   SELECT  ID, Value, ColumnName
    FROM    Prices
            UNPIVOT
            (   Value
                FOR ColumnName IN ([Price_1], [Price_2], [Price_3])
            ) upvt
    WHERE   ISNUMERIC(Value) = 0
)
SELECT  ID,
        BadColumns = STUFF((SELECT  ', ' + ColumnName 
                            FROM    CTE
                            WHERE   CTE.ID = t.ID
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''),
        BadValues = STUFF(( SELECT  ', ' + Value 
                            FROM    CTE
                            WHERE   CTE.ID = t.ID
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM    (SELECT DISTINCT ID FROM CTE) t

第一部分 UNPIVOT 您的查询以将列作为行获取，然后第二部分将“坏”行连接成一列。

Example on SQL Fiddle