从表中获取具有特定值的列名 [英] To get column names from table having a particular value

问题描述

请告诉我如何获取特定行的列值为123的列名。提前提示。

Please tell me How to get column names where column value =123 for a particular row.Thanks in advance .

推荐答案

-- Test table
declare @T table(ID int, Col1 varchar(10), Col2 int, Col3 bit, Col4 varchar(max))
insert into @T values (1, '123', 123, 1, 'Some text long text')

-- ID to get one row
declare @ID int
set @ID = 1

-- Value to search for
declare @Val varchar(10) 
set @Val = '123'

select 
  Col.value('local-name(.)', 'varchar(max)') as ColName
from (select *
      from @T
      where ID = @ID
      for xml path(''), type) as T(XMLCol)
  cross apply 
    T.XMLCol.nodes('*') as n(Col) 
where Col.value('.', 'varchar(10)') = @Val  

结果

ColName
--------
Col1
Col2

这篇关于从表中获取具有特定值的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！