SQL Puzzle | Select columns which doesn’t have all zero value

In this puzzle will be given the table name and you have to get the data from the columns where at least one value is NON Zero. In below example we have 5 columns – Col1,col2,col3,col4 and col5. The requirement is to get data from col1,col3,col4.

Please check out the sample input values and sample expected output below.

Sample Input

Col1 Col2 Col3 Col4 Col5
100 0 0 20 0
100 0 0 10 0
100 0 12 20 0
100 0 0 80 0

Expected Output

Col1 Col3 Col4
100 0 20
100 0 10
100 12 20
100 0 80

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE IgnoreZero ( Col1 INT ,Col2 INT ,Col3 INT ,Col4 INT ,Col5 INT ) GO INSERT INTO IgnoreZero VALUES (100,0,0,20,0) ,(100,0,0,10,0) ,(100,0,12,20,0) ,(100,0,0,80,0) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1

 ``` -- DECLARE @sq AS NVARCHAR(MAX) = '' DECLARE @Cols1 AS VARCHAR(MAX) = '' DECLARE @TableName AS VARCHAR(MAX) = 'IgnoreZero' SELECT @Cols1 = @Cols1 + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName SET @Cols1 = SUBSTRING(@Cols1,0,LEN(@Cols1)) SET @sq = N' DECLARE @Cols AS VARCHAR(MAX) = '''' ; SELECT @Cols = @Cols + cols + '', '' FROM ' + @TableName + ' s UNPIVOT ( Vals for cols in ( ' + @Cols1 + ')) u GROUP BY cols HAVING SUM(Vals) > 0 ; SET @Cols = SUBSTRING(@Cols,0,LEN(@Cols)); SELECT @Cols1 = @Cols ;' EXEC SP_ExecuteSQL @Query = @sq ,@Params = N'@Cols1 VARCHAR(MAX) OUTPUT' ,@Cols1 =@Cols1 OUTPUT EXEC( 'SELECT ' + @Cols1 + ' FROM ' + @TableName) -- ```

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com