再求：变表的列为行

[辉歌]

我曾在该版请教过：
http://community.csdn.net/Expert/topic/4484/4484863.xml?temp=.6107904
zjcxc(邹建) 大哥迅速的解决了问题。

现在情况有所变化。

有一个表，结构如下：
城市名 状态 其他字段（不重要)
杭州 0 ...
杭州 1 ...
杭州 －1 ...
杭州 1 ...
武汉 1 ...
武汉 1 ...
北京 －1 ...
北京 －1 ...

其中城市名字段的值不固定。

现在要求统计这个表：
1，统计武汉，北京，上海，杭州 ，西安，深圳等等14个已知具体城市名的出现的次数。同时将其他城市名成出现的次数汇总到“其他”字段里。
2，对每个城市名，要求统计三种状态下出现的次数。 状态＝1 和状态！＝1，和所有状态
3，对每个城市出现的次数做一个比例计算： 状态1出现的次数/ 所有状态出现的次数

最终，能出现如下一个表：
情况 杭州 武汉 北京 南京 西安深圳,....,其他
通过 111 222 333 555 122 223.... 456
未通过 111 222 333 233 111 89 .... 356
总量 222 444 666 788 234 266... 899
比例 50% 50% 50% 23% 57% 90% ... %65

但是不知道该如何下手。请高手指教。

如果对比例计算和未通过数据统计很麻烦，可以不做。

[zhaoanle]

--建测试表
create table 表A(城市名 varchar(10),状态 float(1,1))
insert 表A values('杭州', 0)
insert 表A values('杭州', 1)
insert 表A values('杭州', -1)
insert 表A values('杭州', 1)
insert 表A values('武汉', 1)
insert 表A values('武汉', 1)
insert 表A values('北京', -1)
insert 表A values('北京', -1)
go
--查询语句
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)

set @sql1='select ''1通过'' as ''情况'','
select @sql1=@sql1+'sum(case when 状态=1 and 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A) a
set @sql1=left(@sql1,len(@sql1)-1)+' from 表A'

set @sql2='select ''2未通过'','
select @sql2=@sql2+'sum(case when 状态!=1 and 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A) a
set @sql2=left(@sql2,len(@sql2)-1)+' from 表A'

set @sql3='select ''3总量'','
select @sql3=@sql3+'sum(case when 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A ) a
set @sql3=left(@sql3,len(@sql3)-1)+' from 表A '

set @sql4='select ''4通过比例(%)'','
select @sql4=@sql4+'sum(case when 状态=1 and 城市名='''+a.城市名+'''then 1 else 0 end)*100/sum(case when 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A) a
set @sql4=left(@sql4,len(@sql4)-1)+' from 表A '

exec (@sql1+' union '+@sql2+' union '+@sql3+' union '+@sql4 +'order by 情况 ')
/*--结果
情况 北京 杭州 武汉
------------ ----------- ----------- -----------
1通过 0 2 2
2未通过 2 2 0
3总量 2 4 2
4通过比例(%) 0 50 100

这样应该可以了吧?*/

[子陌红尘]

create table u(name varchar(20))
insert into u select '北京'
insert into u select '上海'
insert into u select '西安'
insert into u select '深圳'

create table t(city varchar(20),status int)
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1


declare @s varchar(8000),@v varchar(1000)
set @s='select 情况=isnull(status,''总量'')'
set @v=''
select @s=@s+','+name+'=rtrim(sum(case city when '''+name+''' then 1 else 0 end))',@v=@v+','+''''+name+'''' from u

set @s=@s+',其他=rtrim(sum(case when city not in('+stuff(@v,1,1,'')+') then 1 else 0 end))
from
(select status=(case status when 1 then ''未通过'' else ''通过'' end),city from t) a
group by
status with rollup'

exec(@s)

/*
情况 北京 上海 西安 深圳 其他
------ ------ ------ ------ ------ ------
未通过 2 0 0 0 1
通过 0 0 0 0 5
总量 2 0 0 0 6
*/

drop table t,u

[zzit0721]

create table t(city varchar(20),status int)----测试数据
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',-1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1
declare
@sql varchar(1000)--@sql求数量判断值
set @sql=''
select @sql=@sql+','+city+'=isnull(sum(case when city='''+city+''' and status=1 then 1
when city='''+city+''' and status=-1 then 1 end ),0)'
from t
group by city


set @sql='select 情况=isnull(case status when 1 then ''通过'' when -1 then ''未通过'' end,''总量'')'+@sql+'from t group by
status with rollup'


declare
@sq varchar(1000)---@sq求比例
set @sq=''
select @sq=@sq+','+city+'=isnull(convert(float,sum(case when city='''+city+''' and status=-1 then 1 end))/convert(float,sum(case when city='''+city+''' and status=1 then 1 end)),0)'
from t
group by city
set @sq='select 情况=''比例'' '+@sq+' from t'
set @sq=@sql+' union all '+@sq
exec (@sq)
呵呵，午休了才来了，不好意思，晚了哈，楼主

[zzit0721]

呵呵，LZ自己定制下比例的格式就完了

[hbj88]

说明不太清楚！！！！！

[子陌红尘]

提供一个不计算比例的：
----------------------------------------------------------------------------------------------
create table t(city varchar(20),status int)
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1

select
情况=isnull(case status when 1 then '通过' when -1 then '未通过' end,'总量'),
武汉=rtrim(sum(case city when '武汉' then 1 else 0 end)),
北京=rtrim(sum(case city when '北京' then 1 else 0 end)),
上海=rtrim(sum(case city when '上海' then 1 else 0 end)),
杭州=rtrim(sum(case city when '杭州' then 1 else 0 end)),
西安=rtrim(sum(case city when '西安' then 1 else 0 end)),
深圳=rtrim(sum(case city when '深圳' then 1 else 0 end)),
其他=rtrim(sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end))
from
t
group by
status with rollup

/*
情况 武汉 北京 上海 杭州 西安 深圳 其他
------ ------ ------ ------ ------ ------ ------ ------
未通过 0 2 0 1 0 0 0
通过 2 0 0 3 0 0 0
总量 2 2 0 4 0 0 0
*/

drop table t

[lw1a2]

create table #t(city varchar(20),s int)

insert into #t values('杭州',0)
insert into #t values('杭州',1)
insert into #t values('杭州',-1)
insert into #t values('杭州',1)
insert into #t values('武汉',1)
insert into #t values('武汉',1)
insert into #t values('北京',-1)
insert into #t values('北京',-1)


select '通过',
sum(case city when '杭州' then 1 else 0 end) as '杭州',
sum(case city when '武汉' then 1 else 0 end) as '武汉',
sum(case city when '北京' then 1 else 0 end) as '北京'
from #t
where s=1
union all
select '未通过',
sum(case city when '杭州' then 1 else 0 end) as '杭州',
sum(case city when '武汉' then 1 else 0 end) as '武汉',
sum(case city when '北京' then 1 else 0 end) as '北京'
from #t
where s<>1
union all
select '总量',
sum(case city when '杭州' then 1 else 0 end) as '杭州',
sum(case city when '武汉' then 1 else 0 end) as '武汉',
sum(case city when '北京' then 1 else 0 end) as '北京'
from #t
union all
select '比例',
isnull((select sum(1.) from #t where city='杭州' and s=1)/(select sum(1.) from #t where city='杭州'),0),
isnull((select sum(1.) from #t where city='武汉' and s=1)/(select sum(1.) from #t where city='武汉'),0),
isnull((select sum(1.) from #t where city='北京' and s=1)/(select sum(1.) from #t where city='北京'),0)
from #t
group by city

--以上只是3个城市的

[子陌红尘]

create table t(city varchar(20),status int)
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1


select a.*
from
(select
情况=isnull(case status when 1 then '通过' when -1 then '未通过' end,'总量'),
武汉=rtrim(sum(case city when '武汉' then 1 else 0 end)),
北京=rtrim(sum(case city when '北京' then 1 else 0 end)),
上海=rtrim(sum(case city when '上海' then 1 else 0 end)),
杭州=rtrim(sum(case city when '杭州' then 1 else 0 end)),
西安=rtrim(sum(case city when '西安' then 1 else 0 end)),
深圳=rtrim(sum(case city when '深圳' then 1 else 0 end)),
其他=rtrim(sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end))
from
t
group by
status with rollup
union
select
情况='比例',
武汉=rtrim(cast(sum(case when city='武汉' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '武汉' then 1 else 0 end)=0 then 1 else sum(case city when '武汉' then 1 else 0 end) end) as int))+'%',
北京=rtrim(cast(sum(case when city='北京' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '北京' then 1 else 0 end)=0 then 1 else sum(case city when '北京' then 1 else 0 end) end) as int))+'%',
上海=rtrim(cast(sum(case when city='上海' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '上海' then 1 else 0 end)=0 then 1 else sum(case city when '上海' then 1 else 0 end) end) as int))+'%',
杭州=rtrim(cast(sum(case when city='杭州' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '杭州' then 1 else 0 end)=0 then 1 else sum(case city when '杭州' then 1 else 0 end) end) as int))+'%',
西安=rtrim(cast(sum(case when city='西安' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '西安' then 1 else 0 end)=0 then 1 else sum(case city when '西安' then 1 else 0 end) end) as int))+'%',
深圳=rtrim(cast(sum(case when city='深圳' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '深圳' then 1 else 0 end)=0 then 1 else sum(case city when '深圳' then 1 else 0 end) end) as int))+'%',
其他=rtrim(cast(sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') and status=1 then 1 else 0 end)*100.0/
(case when sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end)=0 then 1 else sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end) end) as int))+'%'
from
t) a
order by
(case 情况 when '未通过' then 1 when '通过' then 2 when '总量' then 3 esle 4 end)

/*
情况 武汉 北京 上海 杭州 西安 深圳 其他
------ ------ ------ ------ ------ ------ ------ ------
通过 2 0 0 3 0 0 0
未通过 0 2 0 1 0 0 0
总量 2 2 0 4 0 0 0
比例 100% 0% 0% 75% 0% 0% 0%
*/

drop table t

[wang520d]

顶
libin_ftsafe(子陌红尘) 一看就知道是牛人

[zzit0721]

create table t(city varchar(20),status int)----测试数据
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',-1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1
select distinct city
from t
select *
from t

declare
@sql varchar(1000)
set @sql=''
select @sql=@sql+','+city+'=isnull(sum(case when city='''+city+''' and status=1 then 1
when city='''+city+''' and status=-1 then 1 end ),0)'
from t
group by city


set @sql='select 情况=isnull(case status when 1 then ''通过'' when -1 then ''未通过'' end,''总量'')'+@sql+'from t group by
status with rollup'
exec (@sql)
这是动态的SQL，等我吃饭回来给你考虑下比例哈，呵呵

[辉歌]

SQL SERVER版就是牛人多。

[辉歌]

谢谢楼上的各位大哥。
如果将已知的14个城市名放在表里，该怎么做呢？

表 units
name
北京
上海
西安
深圳
...

另外通过和未通过的状态，
通过的状态是 status ＝1
未通过的状态是 status＝－1，0

[mylover002]

高手，

[caizhen2000_82]

真是牛人一大堆啊！！