CODEWITHSUNDEEP
sqlsql-servert-sql
Surensiveaya
Surensiveaya

Reputation: 347

Get column names from a table matching a specific value

I have a table named TableA which has many rows. Sample structure given below

CID int, Col1 int, Col2 int, Col3 int, Col4 int

when I run a query (say when CID=5) i will get only one row with Col1,Col2 etc having different values.

I want to get the digit of column name where the row value is -1.

For more clarity

CID, Col1, Col2 , Col3 , Col4 
5      0    -1     0       -1

in this example i should get result as 

MyRes
2
4

Is there a any way to achieve it

Upvotes: 0

Views: 2024

Answers (2)

DarkRob
DarkRob

Reputation: 3833

You may use unpivot then filter your record on basis of result value which is -1 in your case.

; with cte as (
select CID, result, col from
(
   select * from table 
) as t
unpivot
(
   result for col in ( Col1, col2, col3, col4 )
) as p
)
select CId, col from cte where result = -1

Just a bit of homework for you to get the number part from the column name.
In case you find any problem in that part please comment I'll do that also but lets give a try first.

Upvotes: 1

Surensiveaya
Surensiveaya

Reputation: 347

I got the answer

declare @ID int
set @ID = 1
select substring( T1.ColName, 2, LEN(T1.ColName)) as MyRes from (
select
  Col.value('local-name(.)', 'varchar(10)') as ColName
from (select *
      from TableA

      for xml path(''), type) as T(XMLCol)
  cross apply 
    T.XMLCol.nodes('*') as n(Col) 
where Col.value('.', 'varchar(10)') = '-1'  
)T1

Upvotes: 0

Related Questions