Query to get column name with specific value

Here is tested schema

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into testtable values ('123','pass',null,'fail','skip')
insert into testtable values ('456','fail',null,'pass','skip')
insert into testtable values ('789',null,'fail','skip','pass')
insert into testtable values ('345','pass','pass','pass','fail')

I would like to fetch the COLUMNNAME where the value is fail.
Basically I need to know which test failed, test1, test2, test3 or
test4?

Is this possible?One method:

SELECT CASE
WHEN test1 = 'fail' THEN 'test1'
WHEN test2 = 'fail' THEN 'test2'
WHEN test3 = 'fail' THEN 'test3'
WHEN test4 = 'fail' THEN 'test4'
END AS Test
FROM testtable

This could also be accomplished with a rather ugly dynamic SQL script but
I'd rather not go there. You might consider revising your schema to
eliminate the repeating data. It's a lot easier to query data when your
data is in 1NF. Suggested alternative:

CREATE TABLE TestTable
(
SerialNumber char (12) NOT NULL,
TestNumber int NOT NULL,
TestResult varchar(10),
CONSTRAINT PK_TestTable PRIMARY KEY(SerialNumber, TestNumber)
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kj" <kjaggi@.hotmail.com> wrote in message
news:665416be.0409071642.3710dac0@.posting.google.c om...
> Here is tested schema
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[TestTable]
> GO
> CREATE TABLE [dbo].[TestTable] (
> [SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testtable values ('123','pass',null,'fail','skip')
> insert into testtable values ('456','fail',null,'pass','skip')
> insert into testtable values ('789',null,'fail','skip','pass')
> insert into testtable values ('345','pass','pass','pass','fail')
>
> I would like to fetch the COLUMNNAME where the value is fail.
> Basically I need to know which test failed, test1, test2, test3 or
> test4?
> Is this possible?|||Thanks. I have already changed the schema. This is legacy stuff I am
trying to clean up but I needed to migrate the data to the new schema.