How to get column name of particular value in sql

问题:

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 .

回答1:

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

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 Fiddle

MS SQL Server 2014 Schema Setup:

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');

Query 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);

Results:

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