Solved

t-sql conditional query

Posted on 2014-03-03
3
345 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

696 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