如何在 sql server 2008 中获取特定值的列名

问题描述

我想获取特定值的列名.我有具有唯一 id 的表 CurrentReport，我现在可以从该行的值中获取特定的行，我想获取我需要更新的列的名称.

I want to get column name of particular value. I have table CurrentReport having unique id from which i can get particular row now from the values of that row i want to get the name of columns that i need to update .

推荐答案

我想你想要一个与特定值匹配的列名列表.

I think you want a list of column names that match a particular value.

为此，您可以在交叉应用中为每一行创建一个 XML 列，并在第二个交叉应用中使用 nodes() 来分解具有您正在寻找的值的元素.

To do that you can create a XML column in a cross apply for each row and the use nodes() in a second cross apply to shred on the elements that has the value you are looking for.

SQL 小提琴

MS SQL Server 2014 架构设置:

create table dbo.CurrentReport
(
  ID int primary key,
  Col1 varchar(10),
  Col2 varchar(10),
  Col3 varchar(10)
);

go

insert into dbo.CurrentReport(ID, Col1, Col2, Col3) values(1, 'Value1', 'Value2', 'Value3');
insert into dbo.CurrentReport(ID, Col1, Col2, Col3) values(2, 'Value2', 'Value2', 'Value2');
insert into dbo.CurrentReport(ID, Col1, Col2, Col3) values(3, 'Value3', 'Value3', 'Value3');

查询 1:

-- Value to look for
declare @Value varchar(10) = 'Value2';

select C.ID,
       -- Get element name from XML
       V.X.value('local-name(.)', 'sysname') as ColumnName
from dbo.CurrentReport as C
  cross apply (
              -- Build XML for each row
              select C.*
              for xml path(''), type
              ) as X(X)
  -- Get the nodes where Value = @Value
  cross apply X.X.nodes('*[text() = sql:variable("@Value")]') as V(X);

结果:

| ID | ColumnName |
|----|------------|
|  1 |       Col2 |
|  2 |       Col1 |
|  2 |       Col2 |
|  2 |       Col3 |

这篇关于如何在 sql server 2008 中获取特定值的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！