SEO

#1 (**permalink**) 02-29-2008, 04:11 AM

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?

#2 (**permalink**) 02-29-2008, 04:11 AM

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?

#3 (**permalink**) 02-29-2008, 04:11 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Query to get column name with specific value