Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [Remove ALL Zero Puzzle] – If all the columns having zero value then don’t show that row. Please check out the sample input and expected output. In this case we have to remove the 5th row while selecting data.

Sample Input

A B C D
0 0 0 1
0 0 1 0
0 1 0 0
1 0 0 0
0 0 0 0
1 1 1 0

Expected Output

A B C D
0 0 0 1
0 0 1 0
0 1 0 0
1 0 0 0
1 1 1 0

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table
CREATE TABLE [dbo].[TestMultipleZero]
(
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[D] [int] NULL
)
GO

--Insert Data
INSERT INTO [dbo].[TestMultipleZero](A,B,C,D)
VALUES (0,0,0,1),(0,0,1,0),(0,1,0,0),(1,0,0,0),(0,0,0,0),(1,1,1,0)

--Check data
SELECT A,B,C,D FROM [dbo].[TestMultipleZero]

Update May 14 | Solutions



--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

SELECT * FROM TestMultipleZero
WHERE A != 0 OR B != 0 OR C != 0 OR D != 0


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com