sql不显示值替0的列

sql不显示值为0的列
以下语句为动态列纵向和横向求和的，如何隐藏纵向和为0的列

SQL code

declare @str varchar(2000)set @str=''select @str=@str+',sum('+name+') as '+namefrom syscolumns where id=object_id('JXhuizong')and name not in('id','JXtime','姓名') declare @col varchar(2000)set @col=''select @col=@col+'+sum('+name+')'from syscolumns where id=object_id('JXhuizong')and name not in('id','JXtime','姓名') print @colset @str='select row_number()over(order by getdate()) as id,姓名'+@str+',(select '+RIGHT(@col,LEN(@col)-1)+' from JXhuizong b where a.姓名=b.姓名 and JXtime='''+@JXtime+''')as 加减总分 from JXhuizong a where JXtime='''+@JXtime+''' group by 姓名 'exec( @str)

执行结果为

1陈会会0000.400000.4
2韩超11.20000002.2
3王昀01.500.200001.7
4张甜0001.500001.5

如何隐藏全是0的列 就是sum（列）是0的不显示

[解决办法]

SQL code

case sum(col)=0 then '' else ltrim(sum(col))
[解决办法]
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    Id INT,    Name VARCHAR(100),    col1 INT,    col2 INT,    col3 INT)GOINSERT INTO tbaSELECT 1,'张三',100,0,48 UNIONSELECT 2,'李四',89,0,48 UNIONSELECT 3,'王五',87,0,43 UNIONSELECT 4,'张三',79,0,47 UNIONSELECT 5,'李四',58,0,46 UNIONSELECT 6,'王五',89,0,55 GODECLARE @sql VARCHAR(1000) = ''DECLARE @Total VARCHAR(1000) = ''DECLARE @ColumnNames TABLE (ID INT IDENTITY(1,1),ColumnName VARCHAR(100))declare @num int, @sqls nvarchar(4000) DECLARE @Line INT = 1DECLARE @TotalNum INTDECLARE @ColumnName VARCHAR(100)INSERT INTO @ColumnNamesSELECT name FROM Sys.columnsWHERE object_name(object_id) = 'tba' and name NOT IN ('ID','Name') SELECT @TotalNum = MAX(ID) FROM @ColumnNamesWHILE @Line <= @TotalNumBEGIN    SELECT @ColumnName = ColumnName    FROM @ColumnNames    WHERE ID = @Line    set @sqls='select @a=SUM(' + @ColumnName + ') from tba '     exec sp_executesql @sqls,N'@a int output',@num output         IF @num = 0    BEGIN        DELETE FROM @ColumnNames WHERE ID = @Line    END     SET @Line = @Line + 1 ENDSET @sql = 'SELECT Name'SELECT @sql = @sql + ',' + 'SUM(' + ColumnName + ') AS ' + ColumnName,@Total = @Total + '+' + 'SUM(' + ColumnName + ')'FROM @ColumnNamesSET @sql = @sql + ',' + RIGHT(@Total,LEN(@Total) - 1) + ' AS Total' + ' FROM tba GROUP BY Name'EXEC (@Sql)Name    col1    col3    Total李四    147    94    241王五    176    98    274张三    179    95    274 

 

查看更多 下一篇