| re: Query to get column name with specific value
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...[color=blue]
> 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?[/color]
| 
