如何在 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 |
相关文章