how to not show columns having zero records in sql ?

Subin Thomas

7y2.7kSQL

i have a table having more than 8 columns

when i search users this 8 column appears some column has zero records in it so i dont want to show the column having zero value,

i want to show the clumn having records

what logic is needed to put in sql to get such output

if anyone can help,

thanks in advance

10 Replies

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

Laxmidhar Sahoo7y ago
Accepted answer
Then search count records with not null against the not null value of each column.

when count more than select the column
+2
Nirav Gandhi7y ago
use below dynamic query which will work for any table in the 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+'');
+1
Subin Thomas7y ago
yes laxmidhar indeed
+1
Laxmidhar Sahoo7y ago
hi subin

It is a good technique solved by like a team work .fine

IT may help to oyhers .So accept the answer

Thanks
+2
Subin Thomas7y ago
yes laxmidhar i resolved it, its working i used operator and some changes in my query and i got the desired output.
well thanks everyone for sharing ideas
+1
Amit Kumar7y ago
Hi, Your question is not clear can you telll me onething. let's suppose you have search data from your table and it returns 4 rows with 8 column and 2nd & 3rd row contains 0 value in the column4. and same column4 contains not 0 value for row 1st and 4th then what will you do. in this situation column4 should display or not. Can you tell me?
+2
Laxmidhar Sahoo7y ago
Thanks priyanka and iis it working subin
+2
Laxmidhar Sahoo7y ago
Hi both priyanka and rajnikant

you all are in right direction.So thanks

From your post I got a solution that dynamic query execute from a stored procedure

I took 3 variables and bellow given
declare @ac int
declare @bc int
declare @cc int
declare @que varchar(max)
set @que  = 'select' + '  '
select @ac = count(*) from tablename where afieldname IS NOT NULL

select @bc = count(*) from tablename where bfieldname IS NOT NULL

select @cc = count(*) from tablename where cfieldname IS NOT NULL


IF(@ac > 0)
  BEGIN
     set @que = @que + 'afieldname' + '  '
END
IF(@bc > 0)
  BEGIN
     set @que = @que + ','+ 'bfieldname' + '  '
END
IF(@cc > 0)
  BEGIN
     set @que = @que + ','+ 'cfieldname' + '  '
END
  set @que = @que + 'from ' + '   ' + 'tablename'

EXEC(@que)
+2
Rajanikant Hawaldar7y ago
Hi Subin Thomus,

I will give idea, u implement it.
Create stored procedure, declare 8 variables, then you write select query to get data then check that column has data or not, if have set to @col1 variable then do the same for 8 columns and don't set if don't have data

lets assume col3,col5,col6,col7 don't have data then you final query to return required colums means which returns only columns which has data

select @COL1,@COL2,@COL4,@COL48

just think how many queries you need to write, check how to write stored procedure and if conditions in stored procedure and how to write multile queries in strored procedure to concate and send it as final data what you want .
+2
Subin Thomas7y ago
can you explain with syntax

for eg
query is "select * from table "
this table has field suppose a,b,c,d,e,f

how will the code be
+1