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

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 @retval NVARCHAR(MAX) DECLARE @retvalOUT NVARCHAR(MAX) DECLARE @retvalFinal NVARCHAR(MAX) DECLARE @sSQL nvarchar(MAX); DECLARE @col nvarchar(MAX); DECLARE @ParmDefinition nvarchar(MAX); SET @col = N'SELECT @retvalOUT = cols from (SELECT TOP 1 STUFF((SELECT ' +' '' + CASE WHEN MIN(COALESCE('' + name + '',0)) = 0 AND ''' + '+'+'''MAX(COALESCE('' + name + '',0))=0 THEN '''''''' ELSE '''','' + name + '''''' END '' FROM sys.columns WHERE [object_id] = OBJECT_ID(''dbo.IgnoreZero'')FOR XML PATH('''')),1,1,'''')cols)A' SET @ParmDefinition = N'@retvalOUT NVARCHAR(MAX) OUTPUT'; EXEC sp_executesql @col, @ParmDefinition, @retvalOUT=@retval OUTPUT; SET @retvalFinal = 'SELECT ' + @retval + 'From IgnoreZero' DECLARE @Results TABLE(Columns Nvarchar(MAX)) INSERT @Results(Columns) EXEC sp_executesql @retvalFinal, @ParmDefinition, @retvalOUT=@retvalfinal DECLARE @t NVARCHAR(MAX) = (SELECT top 1 SUBSTRING(Columns, 2, LEN(Columns) - 1) from @results) SET @sSQL = 'Select ' + @t + ' FROM IgnoreZero' EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retvalfinal OUTPUT -- ```

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