求交叉表并求和语句
求交叉表并求和语句
我用SQL SERVER 2000
我有一表FGSSB :
XRQ RGL JCMC
2012-04-01 26896 二分公司
2012-04-01 22330 三分公司
2012-04-01 25919 四分公司
2012-04-02 26913 二分公司
2012-04-02 22267 三分公司
2012-04-02 25355 四分公司
2012-04-03 26527 二分公司
2012-04-03 22442 三分公司
2012-04-03 25934 四分公司
2012-04-04 27043 二分公司
2012-04-04 22678 三分公司
2012-04-04 25918 四分公司
2012-04-05 26764 二分公司
2012-04-05 23056 三分公司
2012-04-05 26858 四分公司
转换并添加至另一表FGSHB 并横向求和，竖向求和
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('[FGSSB]') is not null drop table [FGSSB]gocreate table [FGSSB]([XRQ] datetime,[RGL] int,[JCMC] varchar(8))insert [FGSSB]select '2012-04-01',26896,'二分公司' union allselect '2012-04-01',22330,'三分公司' union allselect '2012-04-01',25919,'四分公司' union allselect '2012-04-02',26913,'二分公司' union allselect '2012-04-02',22267,'三分公司' union allselect '2012-04-02',25355,'四分公司' union allselect '2012-04-03',26527,'二分公司' union allselect '2012-04-03',22442,'三分公司' union allselect '2012-04-03',25934,'四分公司' union allselect '2012-04-04',27043,'二分公司' union allselect '2012-04-04',22678,'三分公司' union allselect '2012-04-04',25918,'四分公司' union allselect '2012-04-05',26764,'二分公司' union allselect '2012-04-05',23056,'三分公司' union allselect '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) tset @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 751452012-04-02 26913 22267 25355 745352012-04-03 26527 22442 25934 749032012-04-04 27043 22678 25918 756392012-04-05 26764 23056 26858 76678竖向求和 134143 112773 129984 376900(6 行受影响)**/
[解决办法]
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tbGOCREATE TABLE tb (XRQ datetime, RGL int, JCMC nvarchar(48))goINSERT INTO tbSELECT '2012-04-01', 26896, '二分公司' UNION ALLSELECT '2012-04-01', 22330 , '三分公司' UNION ALLSELECT '2012-04-01', 25919 , '四分公司' UNION ALLSELECT '2012-04-02', 26913 , '二分公司' UNION ALLSELECT '2012-04-02', 22267 , '三分公司' UNION ALLSELECT '2012-04-02', 25355 , '四分公司' UNION ALLSELECT '2012-04-03', 26527 , '二分公司' UNION ALLSELECT '2012-04-03', 22442 , '三分公司' UNION ALLSELECT '2012-04-03', 25934 , '四分公司' UNION ALLSELECT '2012-04-04', 27043 , '二分公司' UNION ALLSELECT '2012-04-04', 22678 , '三分公司' UNION ALLSELECT '2012-04-04', 25918 , '四分公司' UNION ALLSELECT '2012-04-05', 26764 , '二分公司' UNION ALLSELECT '2012-04-05', 23056 , '三分公司' UNION ALLSELECT '2012-04-05', 26858 , '四分公司';;with T as( select * from tb pivot ( max(RGL) for JCMC in(二分公司, 三分公司, 四分公司) ) a)select T.*, [日合计]= [二分公司] + [三分公司] + [四分公司]from Tunion allselect Null, sum([二分公司]), sum([三分公司]),sum([四分公司]), sum([二分公司])+ sum([三分公司])+sum([四分公司])from T/*XRQ 二分公司 三分公司 四分公司 日合计----------------------- ----------- ----------- ----------- -----------2012-04-01 00:00:00.000 26896 22330 25919 751452012-04-02 00:00:00.000 26913 22267 25355 745352012-04-03 00:00:00.000 26527 22442 25934 749032012-04-04 00:00:00.000 27043 22678 25918 756392012-04-05 00:00:00.000 26764 23056 26858 76678NULL 134143 112773 129984 376900*/
[解决办法]
--> 测试数据：[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 allselect '2012-04-01',22330,'三分公司' union allselect '2012-04-01',25919,'四分公司' union allselect '2012-04-02',26913,'二分公司' union allselect '2012-04-02',22267,'三分公司' union allselect '2012-04-02',25355,'四分公司' union allselect '2012-04-03',26527,'二分公司' union allselect '2012-04-03',22442,'三分公司' union allselect '2012-04-03',25934,'四分公司' union allselect '2012-04-04',27043,'二分公司' union allselect '2012-04-04',22678,'三分公司' union allselect '2012-04-04',25918,'四分公司' union allselect '2012-04-05',26764,'二分公司' union allselect '2012-04-05',23056,'三分公司' union allselect '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 751452012-04-02 26913 22267 25355 745352012-04-03 26527 22442 25934 749032012-04-04 27043 22678 25918 756392012-04-05 26764 23056 26858 76678竖向求和 134143 112773 129984 376900*/