Tags

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


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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