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 12194
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" <kj****@hotmail.com> wrote in message
news:66**************************@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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ion |
last post by:
Hi all,
I have a query that takes almost 1 hour to complete. This is acceptable
in certain situations, but unacceptable when no rows should qualify.
Something like:
Select list
>From...
|
by: GL |
last post by:
Hi,
Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have...
|
by: Robert |
last post by:
I am trying to create a db for service providers by county. I'm relatively
new to db programming, but I have done quite a bit of programming ranging
from the old basic days up to doing some...
|
by: Patrick A |
last post by:
All -
I have a query in which I want to look at a form, and match against 1
value if a specific value has been selected from a list box, or another
value if it has not. I'll have similar...
|
by: Venk |
last post by:
hi all,
I saw one reply to arun on the subject "Dynamic Query in Ms-Access" by
one Mr Rick
I found it very useful.
Now to extend this solution forward I have the following situation.
I...
|
by: southoz |
last post by:
Good ay all ,
I'm fairly new to access(a little over 5 weeks now). Since I'v started I have picked up a lot of useful information from forums such as this and in doing so will share that information...
|
by: Stan |
last post by:
I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for...
|
by: Joe Meng |
last post by:
Greetings,
I've seen this question asked and answered here, just not completely yet.
I'm wondering how to use a column value as a table name in another query. So far it's looking like you must...
|
by: RCapps |
last post by:
When running the below SQL Query I keep getting the following error:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table...
|
by: lenygold via DBMonster.com |
last post by:
Hi everybody!
This query is supposed to count consecutive years from the current year
without OLAP.
Input Table:
ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |