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

Subin Thomas

7y858SQL

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nirav Gandhi7y ago
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+'');
0
7y ago
Hello, this is simple solution for ignoring specific columns.

create view view_name as
select abcd,pqr,abc
from table_name
0
Sanwar Ranwa7y ago
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)
0