跪求 ASP.NET + sql 统计问题

[feeling7733]

我有这样一张表：

id       mingcheng         quyu          jinE       shuliang
1        龙城科技          洪山区         900          5
2        龙城科技          江夏区        1200          3
3        海信科技          新州区         800          8
4        海信科技          新州区         600          6
5        海信科技          洪山区         500          10

我想统计成以下的格式：
mingcheng        stat                                     quyu

                                       洪山区             江夏区              新州区
            jinE  shuliang         jinE  shuliang     jinE  shuliang      jinE  shuliang
龙城科技    2100      8             900     5         1200      3           0      0
海信科技    1900     24             500     10          0       0         1400     14  
合计        4000     32            1400     15        1200      3         1400     14   

哪位高手帮我解决下。
也可以用程序来做，（ASP.NET）

[宝_爸]

嘿嘿,搞定,顺便学习了下Pivot
源表和lz一样,只是转成了英文:
1 longcheng hongshan 900 5
2 longcheng jiangxia 1200 3
4 haixin xinzhou 800 8
9 haixin xinzhou 600 6
10 haixin hongshan 500 10

输出:
haixin 1900 24 500 10 0 0 1400 14
longcheng 2100 8 900 5 1200 3 0 0
sum 4000 32 1400 15 1200 3 1400 14

代码:

DECLARE @t TABLE (mingcheng nvarchar(50),
				  total int,
				  hongshang int,
				  jiangxia int,
				  xinzhou int);

insert into @t
select a.mingcheng, (sum(a.hongshan)+sum(a.jiangxia)+sum(a.xinzhou))as total, sum(a.hongshan) as hongshan, sum(a.jiangxia) as jiangxia, sum(a.xinzhou) as xinzhou from
(
select mingcheng, IsNull([hongshan],0) as hongshan,IsNull([jiangxia],0) as jiangxia,IsNull([xinzhou],0) as xinzhou from 
Transform PIVOT
(
	sum(jine)
	FOR quyu IN
	( hongshan, jiangxia, xinzhou )
)AS pvt
) as a
group by a.mingcheng

DECLARE @t1 TABLE (mingcheng nvarchar(50),
				  total int,
				  hongshang int,
				  jiangxia int,
				  xinzhou int);

insert into @t1

select a.mingcheng, (sum(a.hongshan)+sum(a.jiangxia)+sum(a.xinzhou))as total, sum(a.hongshan) as hongshan, sum(a.jiangxia) as jiangxia, sum(a.xinzhou) as xinzhou from
(
select mingcheng, IsNull([hongshan],0) as hongshan,IsNull([jiangxia],0) as jiangxia,IsNull([xinzhou],0) as xinzhou from 
Transform PIVOT
(
	sum(shuliang)
	FOR quyu IN
	( hongshan, jiangxia, xinzhou )
)AS pvt
) as a
group by a.mingcheng

select a.mingcheng, a.total, b.total, a.hongshang, b.hongshang, a.jiangxia, b.jiangxia, a.xinzhou, b.xinzhou from @t a
inner join @t1 b
on a.mingcheng = b.mingcheng

union

select 'sum', sum(a.total), sum(b.total), sum(a.hongshang), sum(b.hongshang), sum(a.jiangxia), sum(b.jiangxia), sum(a.xinzhou), sum(b.xinzhou) from @t a
inner join @t1 b
on a.mingcheng = b.mingcheng

[zhongxingqiche]

帮顶了！！！

[wl_bdqn]

揭贴给分

[zhxingway]

上上楼的好复杂啊

case when then else

[sijin]

学习下

[jiangshun]

-----------------------------------------

--> 测试时间：2009-07-15
--> 我的淘宝：http://shop36766744.taobao.com/

--------------------------------------------------

if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[mingcheng] varchar(8),[quyu] varchar(6),[jinE] int,[shuliang] int)
insert [TB]
select 1,'龙城科技','洪山区',900,5 union all
select 2,'龙城科技','江夏区',1200,3 union all
select 3,'海信科技','新州区',800,8 union all
select 4,'海信科技','新州区',600,6 union all
select 5,'海信科技','洪山区',500,10

declare @s nvarchar(4000)
set @s=''
Select 	@s=@s+',['+quyu+'jinE]=max(case when [quyu]='+quotename([quyu],'''')+' then [jinE] else 0 end),
	['+quyu+'shuliang]=max(case when [quyu]='+quotename([quyu],'''')+' then [shuliang] else 0 end)'
from TB group by[quyu]
set @s='select [mingcheng],jinE=sum(jinE),shuliang=sum(shuliang)'+@s+' from TB group by [mingcheng] union all '

declare @t nvarchar(4000)
set @t=''
select @t=@t+',sum(case when [quyu]='+quotename([quyu],'''')+' then [jinE] else 0 end),
	sum(case when [quyu]='+quotename([quyu],'''')+' then [shuliang] else 0 end)' from TB group by quyu
set @t='select ''总计'',sum(jinE),sum(shuliang)'+@t+'from TB'

exec(@s+@t)

/*
mingcheng jinE        shuliang    洪山区jinE     洪山区shuliang 江夏区jinE     江夏区shuliang 新州区jinE     新州区shuliang 
--------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
海信科技      1900        24          500         10          0           0           800         8
龙城科技      2100        8           900         5           1200        3           0           0
总计        4000        32          1400        15          1200        3           1400        14


*/