How to select all values that are not numeric across multiple columns in SQL Server?

Question

I have one table Prices

ID Price_1 Price_2 Price_3

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

---

As you can see from above, some values from columns Price_1, Price_2 and Price_3 might not be numeric.

What I want is that first all find all those non-numeric values and then give an summary (concatenate all non-numeric values and columns for one id)

So for the above example, what I want is

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

---

How should i write this query?

Answer 1

You could do something like this:

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

The first part UNPIVOTs your query to get columns as rows, then the second part will concatenate the "bad" rows into one column.

Example on SQL Fiddle