34,588
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([XRQ] datetime,[RGL] int,[JCMC] varchar(8))
insert [tbl]
select '2012-04-01',26896,'二分公司' union all
select '2012-04-01',22330,'三分公司' union all
select '2012-04-01',25919,'四分公司' union all
select '2012-04-02',26913,'二分公司' union all
select '2012-04-02',22267,'三分公司' union all
select '2012-04-02',25355,'四分公司' union all
select '2012-04-03',26527,'二分公司' union all
select '2012-04-03',22442,'三分公司' union all
select '2012-04-03',25934,'四分公司' union all
select '2012-04-04',27043,'二分公司' union all
select '2012-04-04',22678,'三分公司' union all
select '2012-04-04',25918,'四分公司' union all
select '2012-04-05',26764,'二分公司' union all
select '2012-04-05',23056,'三分公司' union all
select '2012-04-05',26858,'四分公司'
declare @str varchar(8000)
set @str=''
declare @sql varchar(8000)
set @sql=''
select @str=@str+','+[JCMC]+'=max(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'
from tbl group by [JCMC]
select @sql=@sql+','+[JCMC]+'=sum(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'
from tbl group by [JCMC]
exec('select convert(varchar(10),[XRQ],120) as [XRQ]'+@str+
',sum([RGL]) as 横向和 from tbl group by [XRQ] union all
select ''竖向求和'''+@sql+',sum([RGL]) from tbl')
/*
XRQ 二分公司 三分公司 四分公司 横向和
2012-04-01 26896 22330 25919 75145
2012-04-02 26913 22267 25355 74535
2012-04-03 26527 22442 25934 74903
2012-04-04 27043 22678 25918 75639
2012-04-05 26764 23056 26858 76678
竖向求和 134143 112773 129984 376900
*/
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb (XRQ datetime, RGL int, JCMC nvarchar(48))
go
INSERT INTO tb
SELECT '2012-04-01', 26896, '二分公司' UNION ALL
SELECT '2012-04-01', 22330 , '三分公司' UNION ALL
SELECT '2012-04-01', 25919 , '四分公司' UNION ALL
SELECT '2012-04-02', 26913 , '二分公司' UNION ALL
SELECT '2012-04-02', 22267 , '三分公司' UNION ALL
SELECT '2012-04-02', 25355 , '四分公司' UNION ALL
SELECT '2012-04-03', 26527 , '二分公司' UNION ALL
SELECT '2012-04-03', 22442 , '三分公司' UNION ALL
SELECT '2012-04-03', 25934 , '四分公司' UNION ALL
SELECT '2012-04-04', 27043 , '二分公司' UNION ALL
SELECT '2012-04-04', 22678 , '三分公司' UNION ALL
SELECT '2012-04-04', 25918 , '四分公司' UNION ALL
SELECT '2012-04-05', 26764 , '二分公司' UNION ALL
SELECT '2012-04-05', 23056 , '三分公司' UNION ALL
SELECT '2012-04-05', 26858 , '四分公司';
;with T as
(
select *
from tb
pivot
(
max(RGL) for JCMC
in(二分公司, 三分公司, 四分公司)
) a
)
select T.*, [日合计]= [二分公司] + [三分公司] + [四分公司]
from T
union all
select Null, sum([二分公司]), sum([三分公司]),sum([四分公司]), sum([二分公司])+ sum([三分公司])+sum([四分公司])
from T
/*
XRQ 二分公司 三分公司 四分公司 日合计
----------------------- ----------- ----------- ----------- -----------
2012-04-01 00:00:00.000 26896 22330 25919 75145
2012-04-02 00:00:00.000 26913 22267 25355 74535
2012-04-03 00:00:00.000 26527 22442 25934 74903
2012-04-04 00:00:00.000 27043 22678 25918 75639
2012-04-05 00:00:00.000 26764 23056 26858 76678
NULL 134143 112773 129984 376900
*/
if object_id('[FGSSB]') is not null drop table [FGSSB]
go
create table [FGSSB]([XRQ] datetime,[RGL] int,[JCMC] varchar(8))
insert [FGSSB]
select '2012-04-01',26896,'二分公司' union all
select '2012-04-01',22330,'三分公司' union all
select '2012-04-01',25919,'四分公司' union all
select '2012-04-02',26913,'二分公司' union all
select '2012-04-02',22267,'三分公司' union all
select '2012-04-02',25355,'四分公司' union all
select '2012-04-03',26527,'二分公司' union all
select '2012-04-03',22442,'三分公司' union all
select '2012-04-03',25934,'四分公司' union all
select '2012-04-04',27043,'二分公司' union all
select '2012-04-04',22678,'三分公司' union all
select '2012-04-04',25918,'四分公司' union all
select '2012-04-05',26764,'二分公司' union all
select '2012-04-05',23056,'三分公司' union all
select '2012-04-05',26858,'四分公司'
go
-->数据查询:
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when jcmc='''+jcmc+''' then rgl else 0 end) as ['+jcmc+']'
from
(select distinct jcmc from FGSSB) t
set @sql='select isnull(convert(varchar(10),xrq,120),''竖向求和'') as xrq,'+@sql
+',sum(rgl) as [横向和] from FGSSB group by convert(varchar(10),xrq,120) with rollup'
exec (@sql)
/**
xrq 二分公司 三分公司 四分公司 横向和
---------- ----------- ----------- ----------- -----------
2012-04-01 26896 22330 25919 75145
2012-04-02 26913 22267 25355 74535
2012-04-03 26527 22442 25934 74903
2012-04-04 27043 22678 25918 75639
2012-04-05 26764 23056 26858 76678
竖向求和 134143 112773 129984 376900
(6 行受影响)
**/