以下语句为动态列纵向和横向求和的，如何隐藏纵向和为0的列
- SQL code
declare @str varchar(2000)set @str=''select @[email protected]+',sum('+name+') as '+namefrom syscolumns where id=object_id('JXhuizong')and name not in('id','JXtime','姓名') declare @col varchar(2000)set @col=''select @[email protected]+'+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,姓名'[email protected]+',(select '+RIGHT(@col,LEN(@col)-1)+' from JXhuizong b where a.姓名=b.姓名 and [email protected]+''')as 加减总分 from JXhuizong a where [email protected]+''' group by 姓名 'exec( @str)
执行结果为
1 陈会会 0 0 0 0.4 0 0 0 0 0.4
2 韩超 1 1.2 0 0 0 0 0 0 2.2
3 王昀 0 1.5 0 0.2 0 0 0 0 1.7
4 张甜 0 0 0 1.5 0 0 0 0 1.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,[email protected] 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