Select non zero multiple columns from select statement

Question

1.00/5 (1 vote)

See more:

Below is Sql ResultSet Table

Dates         Col1       Col2       Col3

17-2-2017      0          1          0  
18-2-2017      1          1          0
19-2-2017      1          1          0

I have get result like this below

Dates        col1     col2

17-2-2017     0         1
18-2-2017     1         1
19-2-2017     1         1

What I have tried:

SQL

SELECT p.*
FROM Data p 
INNER JOIN (SELECT A.Date 
            FROM (SELECT p.Date,p.Col1,p.Col2 
 ) As A
            GROUP BY A.Date 
            HAVING COUNT(*) > 1

Posted 17-Feb-17 2:29am

Member 12085957

Updated 17-Feb-17 19:04pm

Bryian Tan

v2

Add a Solution

Comments

OriginalGriff 17-Feb-17 8:36am

And?
What did you want to get?

Tomas Takac 17-Feb-17 9:20am

So you want to exclude Col3 because there are all zeroes? Why? I'm asking that SQL is good at filtering data row-wise but not column-wise. I guess it can be done - just make sure it's worth the effort. Seems like a very odd requirement to me.

Bryian Tan 17-Feb-17 10:54am

Can you provide some sample output that you would expect to see?

Karthik_Mahalingam 17-Feb-17 23:05pm

he just wanted to remove the col3, since it has all zeros

Member 12085957 18-Feb-17 0:22am

Yes

Member 12085957 18-Feb-17 0:22am

I want to exclude col3 from result set because it has all zeros

Karthik_Mahalingam 18-Feb-17 0:28am

how many columns

Member 12085957 18-Feb-17 0:30am

Above result set has 3 columns i want to select columns which doesn't have zeros

Karthik_Mahalingam 18-Feb-17 0:37am

ok

Member 12085957 18-Feb-17 0:52am

i can select col1,col2 from table but i need a dynamic sql to filter column wise

Karthik_Mahalingam 18-Feb-17 1:04am

for dynamic you shall use cursor. however check the solution

Karthik_Mahalingam 18-Feb-17 1:03am

check the solution

Member 12085957 18-Feb-17 1:14am

ok thanq karthik i will check nd i'll update you

Karthik_Mahalingam 18-Feb-17 1:38am

sure

Member 12085957 16-Mar-17 9:24am

@Karthik can you look below link
https://www.codeproject.com/Questions/1176398/Playing-audio-file-from-directory-in-ASP-NET

Karthik_Mahalingam 19-Mar-17 14:10pm

is the below solution works?

Member 12085957 20-Mar-17 2:44am

Yes kathik thanks for your help

Karthik_Mahalingam 20-Mar-17 2:53am

can you close this by marking it as answer

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-02-17T19:03:00

This is not a perfect solution, but it works

Create a temp table and copy the actual data to the temp table, check if the column has all zeros, if so then drop the column from temp table

if exists (     select  * from tempdb.dbo.sysobjects o    where o.xtype in ('U')    and o.id = object_id(N'tempdb..#tbltemp'))
DROP TABLE #tbltemp; 
CREATE TABLE #tbltemp( Dates varchar(9),col1 int, col2 int, col3 int)
insert   into #tbltemp (dates,col1,col2,col3     )   select  dates,col1,col2,col3 from Table1 ;
 
 
 if not exists ( select * from #tbltemp where col3 <> 0 )
 ALTER TABLE #tbltemp DROP COLUMN col3
 if not exists ( select * from #tbltemp where col2 <> 0 )
 ALTER TABLE #tbltemp DROP COLUMN col2
 if not exists ( select * from #tbltemp where col1 <> 0 )
 ALTER TABLE #tbltemp DROP COLUMN col1

 

  select * from #tbltemp