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

i have 1 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 can see above, values columns price_1, price_2 , price_3 might not numeric.

what want first find non-numeric values , give summary (concatenate non-numeric values , columns 1 id)

so above example, want is

id   bad_columns      bad_values p3   price_1,price_3  aa,bb p4   price_2          cc 

---

how should write query?

you this:

with cte (   select  id, value, columnname        prices             unpivot             (   value                 columnname in ([price_1], [price_2], [price_3])             ) upvt       isnumeric(value) = 0 ) select  id,         badcolumns = stuff((select  ', ' + columnname                                 cte                               cte.id = t.id                             xml path(''), type                         ).value('.', 'nvarchar(max)'), 1, 2, ''),         badvalues = stuff(( select  ', ' + value                                 cte                               cte.id = t.id                             xml path(''), type                         ).value('.', 'nvarchar(max)'), 1, 2, '')    (select distinct id cte) t 

the first part unpivots query columns rows, second part concatenate "bad" rows 1 column.

example on sql fiddle