以下语句为动态列纵向和横向求和的,如何隐藏纵向和为0的列declare @str varchar(2000)
set @str=''
select @str=@str+',sum('+name+') as '+name
from 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 @col
set @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 陈会会 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的不显示
set @str=''
select @str=@str+',sum('+name+') as '+name
from 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 @col
set @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 陈会会 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的不显示
case sum(col)=0 then '' else ltrim(sum(col))
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
Id INT,
Name VARCHAR(100),
col1 INT,
col2 INT,
col3 INT
)
GO
INSERT INTO tba
SELECT 1,'张三',100,0,48 UNION
SELECT 2,'李四',89,0,48 UNION
SELECT 3,'王五',87,0,43 UNION
SELECT 4,'张三',79,0,47 UNION
SELECT 5,'李四',58,0,46 UNION
SELECT 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 = 1
DECLARE @TotalNum INT
DECLARE @ColumnName VARCHAR(100)INSERT INTO @ColumnNames
SELECT name
FROM Sys.columns
WHERE object_name(object_id) = 'tba' and name NOT IN ('ID','Name') SELECT @TotalNum = MAX(ID) FROM @ColumnNamesWHILE @Line <= @TotalNum
BEGIN 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 END
SET @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
不能向局部变量赋予默认值。消息 137,级别 15,状态 2,第 16 行
必须声明标量变量 "@Line"。
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
Id INT,
Name VARCHAR(100),
col1 INT,
col2 INT,
col3 INT
)
GO
INSERT INTO tba
SELECT 1,'张三',100,0,48 UNION
SELECT 2,'李四',89,0,48 UNION
SELECT 3,'王五',87,0,43 UNION
SELECT 4,'张三',79,0,47 UNION
SELECT 5,'李四',58,0,46 UNION
SELECT 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
DECLARE @TotalNum INT
DECLARE @ColumnName VARCHAR(100)SET @Line = 1INSERT INTO @ColumnNames
SELECT name
FROM Sys.columns
WHERE object_name(object_id) = 'tba' and name NOT IN ('ID','Name') SELECT @TotalNum = MAX(ID) FROM @ColumnNamesWHILE @Line <= @TotalNum
BEGIN 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 END
SET @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)
消息 139,级别 15,状态 1,第 0 行
不能向局部变量赋予默认值。
消息 139,级别 15,状态 1,第 0 行
不能向局部变量赋予默认值。
消息 137,级别 15,状态 1,第 37 行
必须声明标量变量 "@sql"。
消息 137,级别 15,状态 2,第 39 行
必须声明标量变量 "@sql"。
消息 137,级别 15,状态 2,第 42 行
必须声明标量变量 "@sql"。
消息 137,级别 15,状态 2,第 44 行
必须声明标量变量 "@Sql"。
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
Id INT,
Name VARCHAR(100),
col1 INT,
col2 INT,
col3 INT
)
GO
INSERT INTO tba
SELECT 1,'张三',100,0,48 UNION
SELECT 2,'李四',89,0,48 UNION
SELECT 3,'王五',87,0,43 UNION
SELECT 4,'张三',79,0,47 UNION
SELECT 5,'李四',58,0,46 UNION
SELECT 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
DECLARE @TotalNum INT
DECLARE @ColumnName VARCHAR(100)SET @Line = 1
SET @sql = ''
SET @Total = ''INSERT INTO @ColumnNames
SELECT name
FROM Sys.columns
WHERE object_name(object_id) = 'tba' and name NOT IN ('ID','Name') SELECT @TotalNum = MAX(ID) FROM @ColumnNamesWHILE @Line <= @TotalNum
BEGIN 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 END
SET @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)2005那就不应该报错了啊,我这都好用的。