Solved

t-sql conditional query

Posted on 2014-03-03
3
342 Views
Last Modified: 2014-03-04
I'm using sql server 2008.

I have a table called AgTestTable.

It looks like this:
my table

If you notice the columns called TestCol1, TestCol2, TestCol3.....

TestCol1 and TestCol2 contain all zeros and TestCol3 contains values that are not zero.

Usually only one of these 3 columns contains values at a time and the other two columns contain all zeros.

Sometimes TestCol1 will contain values and when that is the case TestCol2 and TestCol3 will contain all zeros.

Sometimes TestCol2 will contain values and when that is the case TestCol1 and TestCol3 will contain all zeros.

What I want:

If either TestCol1 Or TestCol2 Or TestCol3 contain zeros values don't show those columns, only show the column that contains values that are not zero.

So I came up with this query:

-- check columns for value of zero and only return cols where value is not zero
if exists (select * from [AgTestTable] where [TestCol3] <> 0 and [TestCol2] <> 0 and [TestCol1] <> 0)
select [ItemID],[TestCol3],[TestCol2],[TestCol1]
from [AgTestTable]
else if exists (select * from [AgTestTable] where [TestCol3] <> 0)
select [ItemID],[TestCol3]
from [AgTestTable]
else if exists (select * from [AgTestTable] where [TestCol2] <> 0)
select [ItemID],[TestCol2]
from [AgTestTable]
else if exists (select * from [AgTestTable] where [TestCol1] <> 0)
select [ItemID],[TestCol1]
from [AgTestTable]

Open in new window


 it gives me my desired result which looks like this:

my result from my query

Here is my SQL Fiddle:

http://sqlfiddle.com/#!3/92489/3


This query works. But is there a better more efficient way to write this query that gives me the same result?
0
Comment
Question by:maqskywalker
3 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 39901186
Hi Sky,

If you're sure that only 1 column contains a value, just add them.

  SELECT id, TestCol1 + TestCol2 + TestCol3 FROM mytable;

Open in new window


If there can be multiple columns that are non-zero, to select the first one use:

  SELECT id,
    case when TestCol1 <> 0 THEN TestCol1
         when TestCol2 <> 0 THEN TestCol2
         when TestCol3 <> 0 THEN TextCol3 end as TestValue
  FROM mytable;

Open in new window


In that query, TestValue will be NULL if all of the columns are zero.


Kent
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39901338
Not as long as you insist on changing the column name to reflect the source column.  If you are okay with having a generic column name like "TestData" then go with one of Kdo's suggestions above.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39901801
The EXISTS has to check the entire table.

Instead, use TOP (1) to limit the check to 1 row only to speed it up, and you can still use the custom queries you have.

You could also use dynamic SQL to get a custom column name in the output.


-- check columns for value of zero and only return cols where value is not zero
if (select top (1) TestCol3 from [AgTestTable]) <> 0
    select [ItemID],[TestCol3]
    from [AgTestTable]
else
if (select top (1) TestCol2 from [AgTestTable]) <> 0
    select [ItemID],[TestCol2]
    from [AgTestTable]
else
if (select top (1) TestCol1 from [AgTestTable]) <> 0
    select [ItemID],[TestCol1]
    from [AgTestTable]
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question