--Create the table and insert values as example.
CREATE TABLE T (ID int,class varchar(10), Emp1 int, Emp2 int,
Emp3 int)
GO
INSERT INTO T VALUES (1, 'T ',1,4,3)
INSERT INTO T VALUES (2, 'T ',2,4,1)
INSERT INTO T VALUES (3, 'S ',3,4,3)
INSERT INTO T VALUES (4, 'S ',4,4,2)
INSERT INTO T VALUES (5, 'S ',5,5,1)
GO
测试数据：
id class emp1 emp2 emp3
1 T 1 4 3
2 T 2 4 1
3 S 3 4 3
4 S 4 4 2
5 S 5 5 1
想要的结果：
class emp1 emp2 emp3
T 1 4 3
T 2 4 1
S 3 4 3
S 4 4 2
S 5 5 1
totalT 3 11 15
totals 12 25 31
说明TotalT 取得是按t分组后sum（emp）的数据，然后横向sum。
分组后数据：
按照T分组 sum（EMP） 3 8 4
最终结果： 3，（3+8）， （11+4）
即 totalT 3 11 15
totals 也一样
如何用动态sql 来查询得到这个结果用SysColumns， 来得到各个列名的ID
应为我列名可能会变化。
谢谢！
------解决方案--------------------
DECLARE
@fd nvarchar(4000),
@lastfd nvarchar(1000),
@s nvarchar(4000)
SELECT
@fd = N ' ',
@s = N ' ',
@lastfd = N '0 '
SELECT
@fd = @fd + N ', ' + QUOTENAME(name),
@lastfd = @lastfd + N ' + ISNULL(SUM( ' + QUOTENAME(name)+ N '), 0) ',
@s = @s + N ', ' + @lastfd
FROM syscolumns
WHERE name LIKE N 'Emp% '
AND id = OBJECT_ID(N 'T ')
EXEC(N '
SELECT class ' + @fd + '
FROM T
UNION ALL
SELECT class = N ' 'total ' ' + class ' + @s + '
FROM T
GROUP BY class ')
------解决方案--------------------
皱哥都来了，lz的问题解决了,该节贴了吧。接分～·
------解决方案--------------------
declare @sql varchar(5000),@sql0 varchar(1000),@sql1 varchar(1000),@sql3 varchar(50)
select @sql= 'SELECT ',@sql0= ' ',@sql1= '0 '
select @[email protected]+name+ ', '
from syscolumns where id=object_id( 't ') and colid> 1 order by colid
select @[email protected]+ '+sum( '+name+ ') ',@[email protected][email protected]+ ', '
from syscolumns where id=object_id( 't ') and colid> 2 order by colid
select @sql3=name from syscolumns where id=object_id( 't ') and colid=2
select @sql=left(@sql,len(@sql)-1)+ ' FROM t union all select ' 'total ' '+ '[email protected]+ ', ' +left(@sql0,len(@sql0)-1)+ ' from t group by '[email protected]
exec(@sql)
--------------
T 1 4 3
T 2 4 1
S 3 4 3
S 4 4 2
S 5 5 1
totalS 12 25 31
totalT 3 11 15