横向sum
--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
测试数据：
idclassemp1emp2emp3
1T143
2T241
3S343
4S442
5S551
想要的结果：
classemp1emp2emp3
T143
T241
S343
S442
S551
totalT31115
totals122531
说明TotalT 取得是按t分组后sum（emp）的数据，然后横向sum。
分组后数据：
按照T分组 sum（EMP）384
最终结果： 3，（3+8）， （11+4）
即 totalT 31115
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 @sql=@sql+name+ ', '
from syscolumns where id=object_id( 't ') and colid> 1 order by colid
select @sql1=@sql1+ '+sum( '+name+ ') ',@sql0=@sql0+@sql1+ ', '
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 ' '+ '+@sql3+ ', ' +left(@sql0,len(@sql0)-1)+ ' from t group by '+@sql3
exec(@sql)
--------------
T143
T241
S343
S442
S551
totalS122531
totalT31115
