Solved

t-sql conditional query

Posted on 2014-03-03
3
335 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:Kdo
Kdo 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:
ScottPletcher 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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now