[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

t-sql conditional query

Posted on 2014-03-03
3
Medium Priority
?
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

656 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