how to not show columns having zero records in sql ?

Question

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

Laxmidhar Sahoo · Answer

Then search count records with not null against the not null value of each column. when count more than select the column

Nirav Gandhi · Answer

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+ '' );

Subin Thomas · Answer

yes laxmidhar indeed

Laxmidhar Sahoo · Answer

hi subin It is a good technique solved by like a team work .fine IT may help to oyhers .So accept the answer Thanks

Subin Thomas · Answer

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

Amit Kumar · Answer

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?

Laxmidhar Sahoo · Answer

Thanks priyanka and iis it working subin

Laxmidhar Sahoo · Answer

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)

Rajanikant Hawaldar · Answer

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 .

Subin Thomas · Answer

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