How to get the column name of a specific value in SQL Server 2008

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

To do this, you can create an XML column in a cross for each row, and the nodes used () in the second cross apply to trimming the elements that have the meaning you are looking for.

SQL Fiddle

Configuring MS SQL Server 2014 schema :

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

      

        
        
        
      

    

Request 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 |