how to show only records having value 0 in sql ?

Question

i have a table in which there are number of fields some fields contain value while some contains 0 value like below table abcd xyz pqr abc rst 23 0 5453 54645 0 45353 0 5456 5646 0 213 0 45 564 0 23 0 546 56 0 from above table all the fields having zero should not be visible only 3 fiels should be visible that is abcd, pqr and abc

Nirav Gandhi · Answer

Hello Subin, use below dynamic query which will work for any table in database.you need to just pass table name in @TableName variable.it will return only for numeric columns. DECLARE @TableName VARCHAR (100)= 'Customer' , @ColumnName VARCHAR (200), @intCnt TINYINT= 1 DECLARE @TblColumName AS TABLE (id TINYINT IDENTITY(1, 1), ColumnName VARCHAR (50) ) DROP TABLE IF EXISTS #TotalColumns CREATE TABLE #TotalColumns(ColumnName VARCHAR (50)) INSERT INTO @TblColumName SELECT C. name FROM sys.tables AS T INNER JOIN sys.columns AS C ON C.object_id = T.object_id WHERE T. name = @TableName WHILE(@intCnt ( SELECT COUNT (*) FROM @TblColumName )) BEGIN SET @ColumnName = '' SELECT @ColumnName = ColumnName FROM @TblColumName WHERE Id = @intCnt EXEC (' Declare @TotalColumnName varchar (200) if(( select Count ( '+@ColumnName+' ) from '+@TableName+' where ISNUMERIC( '+@ColumnName+' )=1 and '+@ColumnName+' >0))>0 Begin insert into #TotalColumns select '' '+@ColumnName+' '' END ') SET @intCnt+=1 END SELECT @ColumnName = STUFF( ( SELECT ',' + '[' +ColumnName+ ']' FROM #TotalColumns FOR XML PATH( '' ) ), 1, 1, '' ) IF(@ColumnName IS NOT NULL ) EXEC ( 'select ' +@ColumnName+ ' from ' +@TableName+ '' );

Parth Munjpara · Answer

Hello, this is simple solution for ignoring specific columns. create view view_name as select abcd,pqr,abc from table_name

Sanwar Ranwa · Answer

Hello Subin check this query CREATE TABLE Ty1(a1 INT ,x INT ,p INT ,ab INT ,r INT ) INSERT INTO Ty1 VALUES ( 23 ,0 , 5453, 54645, 0) INSERT INTO Ty1 VALUES ( 45353 , 0 ,5456 ,5646 ,0) INSERT INTO Ty1 VALUES ( 213 ,0 ,45, 564, 0) INSERT INTO Ty1 VALUES ( 23, 0, 546, 56 , 0) SELECT * FROM Ty1 t DECLARE @s VARCHAR ( MAX ) set @s= 'select ' +( CASE WHEN (( SELECT COUNT (*) FROM Ty1 t WHERE a1>0))>0 THEN 'a1,' ELSE '' END ) +( CASE WHEN (( SELECT COUNT (*) FROM Ty1 t WHERE x>0))>0 THEN 'x,' ELSE '' END ) +( CASE WHEN (( SELECT COUNT (*) FROM Ty1 t WHERE ab>0))>0 THEN 'p,' ELSE '' END ) +( CASE WHEN (( SELECT COUNT (*) FROM Ty1 t WHERE r>0))>0 THEN 'r' ELSE '' END )+ +( CASE WHEN (( SELECT COUNT (*) FROM Ty1 t WHERE ab>0))>0 THEN 'ab' ELSE '' END )+ ' from Ty1' print(@s) EXEC (@s)

abcd	xyz	pqr	abc	rst
23	0	5453	54645	0
45353	0	5456	5646	0
213	0	45	564	0
23	0	546	56	0

how to show only records having value >0 in sql ?