关于交叉表的问题？急！与前面不同？难！

[Chimae]

表一如下：
sGroundNo iIndex iFactorClassSn
-------------------- ----------- --------------
zd001 100 1
zd001 100 2
zd001 99 3
zd001 100 4
zd001 100 5
zd001 100 6
zd001 100 9
zd001 100 12
zd001 100 16
zd001 100 14
zd001 100 15
zd001 100 17
表二如下：
sGroundNo sExampleNo iIndex iFactorClassSn
-------------------- -------------------- ------------ --------------
zd001 12 102.000 1
zd001 12 100.000 2
zd001 12 100.000 3
zd001 12 100.000 4
zd001 12 97.475 5
zd001 12 100.000 6
zd001 12 100.000 9
zd001 12 100.000 12
zd001 12 100.000 16
zd001 12 100.000 14
zd001 12 100.000 15
zd001 12 NULL 17
zd001 13 100.000 1
zd001 13 100.000 2
zd001 13 100.000 3
zd001 13 100.000 4
zd001 13 98.978 5
zd001 13 100.000 6
zd001 13 100.000 9
zd001 13 100.000 12
zd001 13 100.000 16
zd001 13 100.000 14
zd001 13 100.000 15
zd001 13 NULL 17
zd001 14 100.000 1
zd001 14 100.000 2
zd001 14 100.000 3
zd001 14 100.000 4
zd001 14 99.215 5
zd001 14 100.000 6
zd001 14 100.000 9
zd001 14 100.000 12
zd001 14 100.000 16
zd001 14 100.000 14
zd001 14 100.000 15
zd001 14 NULL 17
实现效果如下：
iFactorClassSn zd001 12 13 14 …
1 100 102.000 100.000 100.000 …
2 100 100.000 100.000 100.000 …
3 99 100.000 100.000 100.000 …
4 100 100.000 100.000 100.000 …
5 100 97.475 98.978 99.215 …
6 100 100.000 100.000 100.000 …
9 100 100.000 100.000 100.000 …
12 100 100.000 100.000 100.000 …
16 100 100.000 100.000 100.000 …
14 100 100.000 100.000 100.000 …
15 100 100.000 100.000 100.000 …
17 100 100.000 100.000 100.000 …
请教高手！！！

[CrazyFor]

参考:
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)

declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a

select @sql = left(@sql,len(@sql)-1) + ' from #'

exec(@sql)

drop table #

[caiyunxia]

up

[yoki]

declare @sql varchar(8000)
set @sql = 'select iFactorClassSn,sGroundNo'
select @sql = @sql + ',sum(case sExampleNo when '''+ sExampleNo +'''
then isnull(iIndex,0) else 0 end) ['+ sExampleNo+']'
from (select distinct sExampleNo from t2) as a

select @sql = @sql+ ' from t2 group by iFactorClassSn,sGroundNo'

exec(@sql)

[txlicenhe]

create table t1(sGroundNo char(5),iIndex int,iFactorClassSn int)
insert t1 select 'zd001', 100, 1
union all select 'zd001', 100 , 2
union all select 'zd001', 99 , 3
union all select 'zd001', 100 , 4
union all select 'zd001', 100 , 5
union all select 'zd001', 100 , 6
union all select 'zd001', 100 , 9
union all select 'zd001', 100 , 12
union all select 'zd001', 100 , 16
union all select 'zd001', 100, 14
union all select 'zd001', 100 , 15
union all select 'zd001', 100 , 17

create table t2(sGroundNo char(5), sExampleNo int, iIndex numeric(10,3), iFactorClassSn int)
insert t2 select 'zd001', 12, 102.000, 1
union all select 'zd001', 12 , 100.000 ,2
union all select 'zd001', 12 , 100.000 , 3
union all select 'zd001', 12 , 100.000 , 4
union all select 'zd001', 12 , 97.475 , 5
union all select 'zd001', 12 , 100.000 , 6
union all select 'zd001', 12 , 100.000 , 9
union all select 'zd001', 12 , 100.000, 12
union all select 'zd001', 12 , 100.000 ,16
union all select 'zd001', 12 , 100.000 , 14
union all select 'zd001', 12 , 100.000 , 15
union all select 'zd001', 12 , NULL , 17
union all select 'zd001', 13 , 100.000 , 1
union all select 'zd001', 13 , 100.000 , 2
union all select 'zd001', 13 , 100.000, 3
union all select 'zd001', 13 , 100.000 , 4
union all select 'zd001', 13 , 98.978 , 5
union all select 'zd001', 13 , 100.000 , 6
union all select 'zd001', 13 , 100.000 , 9
union all select 'zd001', 13 ,100.000 , 12
union all select 'zd001', 13, 100.000, 16
union all select 'zd001', 13 , 100.000 ,14
union all select 'zd001', 13 , 100.000 , 15
union all select 'zd001', 13 , NULL , 17
union all select 'zd001', 14 , 100.000 , 1
union all select 'zd001', 14 , 100.000 , 2
union all select 'zd001', 14 , 100.000 , 3
union all select 'zd001', 14 , 100.000, 4
union all select 'zd001', 14 , 99.215 ,5
union all select 'zd001', 14 , 100.000 , 6
union all select 'zd001', 14 , 100.000 , 9
union all select 'zd001', 14 , 100.000 , 12
union all select 'zd001', 14 , 100.000 , 16
union all select 'zd001', 14 , 100.000 , 14
union all select 'zd001', 14 , 100.000, 15
union all select 'zd001', 14 , NULL , 17

declare @sql varchar(8000)
set @sql = 'select t2.iFactorClassSn,t1.iIndex'
select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar) +'''
then isnull(t2.iIndex,0) else 0 end) ['+ cast(sExampleNo as varchar)+']'
from (select distinct sExampleNo from t2) as a

select @sql = @sql+ ' from t2 join t1 on t2.iFactorClassSn = t1.iFactorClassSn group by t2.iFactorClassSn,t1.iIndex
order by t2.iFactorClassSn'

exec(@sql)

iFactorClassSn iIndex 12 13 14
-------------- ----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
1 100 102.000 100.000 100.000
2 100 100.000 100.000 100.000
3 99 100.000 100.000 100.000
4 100 100.000 100.000 100.000
5 100 97.475 98.978 99.215
6 100 100.000 100.000 100.000
9 100 100.000 100.000 100.000
12 100 100.000 100.000 100.000
14 100 100.000 100.000 100.000
15 100 100.000 100.000 100.000
16 100 100.000 100.000 100.000
17 100 .000 .000 .000

[txlicenhe]

declare @sql varchar(8000)
set @sql = 'select t2.iFactorClassSn,t1.iIndex'
select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar) +'''
then isnull(t2.iIndex,0) else 0 end) ['+ cast(sExampleNo as varchar)+']'
from (select distinct sExampleNo from t2) as a

select @sql = @sql+ ' from t2 join t1 on t2.iFactorClassSn = t1.iFactorClassSn group by t2.iFactorClassSn,t1.iIndex
order by t2.iFactorClassSn'

exec(@sql)

[pengdali]

declare @sql varchar(8000)
set @sql = 'select 表二.iFactorClassSn,表一.iIndex'

select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10)) +''' then isnull(表二.iIndex,0) else 0 end) ['+ cast(sExampleNo as varchar(10))+']'
from (select distinct sExampleNo from 表二) as a

exec(@sql+' from 表二,表一 where 表二.iFactorClassSn = 表一.iFactorClassSn group by 表二.iFactorClassSn,表一.iIndex order by 表二.iFactorClassSn')

[Chimae]

谢谢各位高手！非常感谢：）
如果我进一步想得到下面的结果该如何实现！再出一百分！
实现效果如下：
iFactorClassSn 12 13 14 …
1 100/102.000 100/100.000 100/100.000 …
2 100/100.000 100/100.000 100/100.000 …
3 99/100.000 99/100.000 99/100.000 …
4 100/100.000 100/100.000 100/100.000 …
5 100/97.475 100/98.978 100/99.215 …
6 100/100.000 100/100.000 100/100.000 …
9 100/100.000 100/100.000 100/100.000 …
12 100/100.000 100/100.000 100/100.000 …
16 100/100.000 100/100.000 100/100.000 …
14 100/100.000 100/100.000 100/100.000 …
15 100/100.000 100/100.000 100/100.000 …
17 100/100.000 100/100.000 100/100.000 …

[zjcxc]

--得到上面的结果:

declare @s varchar(8000),@s1 varchar(8000)
select @s = '',@s1=''
select @s = @s+',['+sExampleNo+']=sum(case b.sExampleNo when '''
+sExampleNo+''' then isnull(b.iIndex,0) else 0 end)'
,@s1=@s1+',['+sExampleNo+']=aa+cast(['+sExampleNo+'] as varchar)'
from (select distinct sExampleNo=cast(sExampleNo as varchar) from 表2) as a

exec('select iFactorClassSn'+@s1+'from(
select a.iFactorClassSn,aa=cast(a.iIndex as varchar)+''\'''+@s+'
from 表1 a join 表2 b on a.iFactorClassSn=b.iFactorClassSn
group by a.iFactorClassSn,a.iIndex
) a
order by a.iFactorClassSn')

[zjcxc]

--上面的斜线写反了,改一下:
declare @s varchar(8000),@s1 varchar(8000)
select @s = '',@s1=''
select @s = @s+',['+sExampleNo+']=sum(case b.sExampleNo when '''
+sExampleNo+''' then isnull(b.iIndex,0) else 0 end)'
,@s1=@s1+',['+sExampleNo+']=aa+cast(['+sExampleNo+'] as varchar)'
from (select distinct sExampleNo=cast(sExampleNo as varchar) from 表2) as a

exec('select iFactorClassSn'+@s1+'from(
select a.iFactorClassSn,aa=cast(a.iIndex as varchar)+''/'''+@s+'
from 表1 a join 表2 b on a.iFactorClassSn=b.iFactorClassSn
group by a.iFactorClassSn,a.iIndex
) a
order by a.iFactorClassSn')

[zjcxc]

--下面是数据测试

--测试数据
create table t1(sGroundNo char(5),iIndex int,iFactorClassSn int)
insert t1 select 'zd001',100,1
union all select 'zd001',100,2
union all select 'zd001',99,3
union all select 'zd001',100,4
union all select 'zd001',100,5
union all select 'zd001',100,6
union all select 'zd001',100,9
union all select 'zd001',100,12
union all select 'zd001',100,16
union all select 'zd001',100,14
union all select 'zd001',100,15
union all select 'zd001',100,17

create table t2(sGroundNo char(5),sExampleNo int,iIndex numeric(10,3),iFactorClassSn int)
insert t2 select 'zd001',12,102.000,1
union all select 'zd001',12,100.000,2
union all select 'zd001',12,100.000,3
union all select 'zd001',12,100.000,4
union all select 'zd001',12,97.475,5
union all select 'zd001',12,100.000,6
union all select 'zd001',12,100.000,9
union all select 'zd001',12,100.000,12
union all select 'zd001',12,100.000,16
union all select 'zd001',12,100.000,14
union all select 'zd001',12,100.000,15
union all select 'zd001',12,NULL,17
union all select 'zd001',13,100.000,1
union all select 'zd001',13,100.000,2
union all select 'zd001',13,100.000,3
union all select 'zd001',13,100.000,4
union all select 'zd001',13,98.978,5
union all select 'zd001',13,100.000,6
union all select 'zd001',13,100.000,9
union all select 'zd001',13,100.000,12
union all select 'zd001',13,100.000,16
union all select 'zd001',13,100.000,14
union all select 'zd001',13,100.000,15
union all select 'zd001',13,NULL,17
union all select 'zd001',14,100.000,1
union all select 'zd001',14,100.000,2
union all select 'zd001',14,100.000,3
union all select 'zd001',14,100.000,4
union all select 'zd001',14,99.215,5
union all select 'zd001',14,100.000,6
union all select 'zd001',14,100.000,9
union all select 'zd001',14,100.000,12
union all select 'zd001',14,100.000,16
union all select 'zd001',14,100.000,14
union all select 'zd001',14,100.000,15
union all select 'zd001',14,NULL,17

--数据处理
declare @s varchar(8000),@s1 varchar(8000)
select @s = '',@s1=''
select @s = @s+',['+sExampleNo+']=sum(case b.sExampleNo when '''
+sExampleNo+''' then isnull(b.iIndex,0) else 0 end)'
,@s1=@s1+',['+sExampleNo+']=aa+cast(['+sExampleNo+'] as varchar)'
from (select distinct sExampleNo=cast(sExampleNo as varchar) from t2) as a

exec('select iFactorClassSn'+@s1+'from(
select a.iFactorClassSn,aa=cast(a.iIndex as varchar)+''/'''+@s+'
from t1 a join t2 b on a.iFactorClassSn=b.iFactorClassSn
group by a.iFactorClassSn,a.iIndex
) a
order by a.iFactorClassSn')
go

--删除测试
drop table t1,t2

/*--测试结果
iFactorClassSn 12 13 14
-------------- -------------- -------------- --------------
1 100/102.000 100/100.000 100/100.000
2 100/100.000 100/100.000 100/100.000
3 99/100.000 99/100.000 99/100.000
4 100/100.000 100/100.000 100/100.000
5 100/97.475 100/98.978 100/99.215
6 100/100.000 100/100.000 100/100.000
9 100/100.000 100/100.000 100/100.000
12 100/100.000 100/100.000 100/100.000
14 100/100.000 100/100.000 100/100.000
15 100/100.000 100/100.000 100/100.000
16 100/100.000 100/100.000 100/100.000
17 100/0.000 100/0.000 100/0.000
--*/

[pengdali]

declare @sql varchar(8000)
set @sql = 'select 表二.iFactorClassSn,表一.iIndex'

select @sql = @sql + ',cast(表一.iIndex as varchar(10))+''/''+cast(sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10)) +''' then isnull(表二.iIndex,''0'') else ''0'' end) as varchar(10)) ['+ cast(sExampleNo as varchar(10))+']'
from (select distinct sExampleNo from 表二) as a

exec(@sql+' from 表二,表一 where 表二.iFactorClassSn = 表一.iFactorClassSn group by 表二.iFactorClassSn,表一.iIndex order by 表二.iFactorClassSn')

[pengdali]

测试：

create table 表一(sGroundNo char(5),iIndex int,iFactorClassSn int)
insert 表一 select 'zd001', 100, 1
union all select 'zd001', 100 , 2
union all select 'zd001', 99 , 3
union all select 'zd001', 100 , 4
union all select 'zd001', 100 , 5
union all select 'zd001', 100 , 6
union all select 'zd001', 100 , 9
union all select 'zd001', 100 , 12
union all select 'zd001', 100 , 16
union all select 'zd001', 100, 14
union all select 'zd001', 100 , 15
union all select 'zd001', 100 , 17

create table 表二(sGroundNo char(5), sExampleNo int, iIndex numeric(10,3), iFactorClassSn int)
insert 表二 select 'zd001', 12, 102.000, 1
union all select 'zd001', 12 , 100.000 ,2
union all select 'zd001', 12 , 100.000 , 3
union all select 'zd001', 12 , 100.000 , 4
union all select 'zd001', 12 , 97.475 , 5
union all select 'zd001', 12 , 100.000 , 6
union all select 'zd001', 12 , 100.000 , 9
union all select 'zd001', 12 , 100.000, 12
union all select 'zd001', 12 , 100.000 ,16
union all select 'zd001', 12 , 100.000 , 14
union all select 'zd001', 12 , 100.000 , 15
union all select 'zd001', 12 , NULL , 17
union all select 'zd001', 13 , 100.000 , 1
union all select 'zd001', 13 , 100.000 , 2
union all select 'zd001', 13 , 100.000, 3
union all select 'zd001', 13 , 100.000 , 4
union all select 'zd001', 13 , 98.978 , 5
union all select 'zd001', 13 , 100.000 , 6
union all select 'zd001', 13 , 100.000 , 9
union all select 'zd001', 13 ,100.000 , 12
union all select 'zd001', 13, 100.000, 16
union all select 'zd001', 13 , 100.000 ,14
union all select 'zd001', 13 , 100.000 , 15
union all select 'zd001', 13 , NULL , 17
union all select 'zd001', 14 , 100.000 , 1
union all select 'zd001', 14 , 100.000 , 2
union all select 'zd001', 14 , 100.000 , 3
union all select 'zd001', 14 , 100.000, 4
union all select 'zd001', 14 , 99.215 ,5
union all select 'zd001', 14 , 100.000 , 6
union all select 'zd001', 14 , 100.000 , 9
union all select 'zd001', 14 , 100.000 , 12
union all select 'zd001', 14 , 100.000 , 16
union all select 'zd001', 14 , 100.000 , 14
union all select 'zd001', 14 , 100.000, 15
union all select 'zd001', 14 , NULL , 17

declare @sql varchar(8000)
set @sql = 'select 表二.iFactorClassSn,表一.iIndex'

select @sql = @sql + ',cast(表一.iIndex as varchar(10))+''/''+cast(sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10)) +''' then isnull(表二.iIndex,''0'') else ''0'' end) as varchar(10)) ['+ cast(sExampleNo as varchar(10))+']'
from (select distinct sExampleNo from 表二) as a

exec(@sql+' from 表二,表一 where 表二.iFactorClassSn = 表一.iFactorClassSn group by 表二.iFactorClassSn,表一.iIndex order by 表二.iFactorClassSn')

/*
iFactorClassSn iIndex 12 13 14
-------------- ----------- --------------------- --------------------- ---------------------
1 100 100/102.000 100/100.000 100/100.000
2 100 100/100.000 100/100.000 100/100.000
3 99 99/100.000 99/100.000 99/100.000
4 100 100/100.000 100/100.000 100/100.000
5 100 100/97.475 100/98.978 100/99.215
6 100 100/100.000 100/100.000 100/100.000
9 100 100/100.000 100/100.000 100/100.000
12 100 100/100.000 100/100.000 100/100.000
14 100 100/100.000 100/100.000 100/100.000
15 100 100/100.000 100/100.000 100/100.000
16 100 100/100.000 100/100.000 100/100.000
17 100 100/0.000 100/0.000 100/0.000
*/

[txlicenhe]

来晚了。

楼上，以后用我的测试环境要交税哦。 :D

[Chimae]

结分！非常感谢！
在第二个问题的基础上如何生成一记录：
计算各纵向比值之和，有0值不计！谢谢！

[Chimae]

http://expert.csdn.net/Expert/topic/2531/2531670.xml?temp=.5664789
请各位朋友接分！

[boyst]

高手，高論

[wzh1215]

To： txlicenhe(马可)
这么长的测试环境都难不到你，下次我找个更大的你可要帮忙哟！

[zjcxc]

--下面是数据测试

--测试数据
create table t1(sGroundNo char(5),iIndex int,iFactorClassSn int)
insert t1 select 'zd001',100,1
union all select 'zd001',100,2
union all select 'zd001',99,3
union all select 'zd001',100,4
union all select 'zd001',100,5
union all select 'zd001',100,6
union all select 'zd001',100,9
union all select 'zd001',100,12
union all select 'zd001',100,16
union all select 'zd001',100,14
union all select 'zd001',100,15
union all select 'zd001',100,17

create table t2(sGroundNo char(5),sExampleNo int,iIndex numeric(10,3),iFactorClassSn int)
insert t2 select 'zd001',12,102.000,1
union all select 'zd001',12,100.000,2
union all select 'zd001',12,100.000,3
union all select 'zd001',12,100.000,4
union all select 'zd001',12,97.475,5
union all select 'zd001',12,100.000,6
union all select 'zd001',12,100.000,9
union all select 'zd001',12,100.000,12
union all select 'zd001',12,100.000,16
union all select 'zd001',12,100.000,14
union all select 'zd001',12,100.000,15
union all select 'zd001',12,NULL,17
union all select 'zd001',13,100.000,1
union all select 'zd001',13,100.000,2
union all select 'zd001',13,100.000,3
union all select 'zd001',13,100.000,4
union all select 'zd001',13,98.978,5
union all select 'zd001',13,100.000,6
union all select 'zd001',13,100.000,9
union all select 'zd001',13,100.000,12
union all select 'zd001',13,100.000,16
union all select 'zd001',13,100.000,14
union all select 'zd001',13,100.000,15
union all select 'zd001',13,NULL,17
union all select 'zd001',14,100.000,1
union all select 'zd001',14,100.000,2
union all select 'zd001',14,100.000,3
union all select 'zd001',14,100.000,4
union all select 'zd001',14,99.215,5
union all select 'zd001',14,100.000,6
union all select 'zd001',14,100.000,9
union all select 'zd001',14,100.000,12
union all select 'zd001',14,100.000,16
union all select 'zd001',14,100.000,14
union all select 'zd001',14,100.000,15
union all select 'zd001',14,NULL,17

--数据处理
declare @s varchar(8000),@s1 varchar(8000),@s2 varchar(8000)
select @s = '',@s1='',@s2=''
select @s = @s+',['+sExampleNo+']=sum(case b.sExampleNo when '''
+sExampleNo+''' then isnull(b.iIndex,0) else 0 end)'
,@s1=@s1+',['+sExampleNo+']=aa+cast(['+sExampleNo+'] as varchar)'
,@s2=@s2+',['+sExampleNo+']=cast(cast(sum(case ['+sExampleNo
+'] when 0 then 0 else iIndex/['+sExampleNo+'] end) as decimal(20,2)) as varchar)'
from (select distinct sExampleNo=cast(sExampleNo as varchar) from t2) as a

exec('select a.iFactorClassSn,aa=cast(a.iIndex as varchar)+''/'',a.iIndex'+@s+'
into #t from t1 a join t2 b on a.iFactorClassSn=b.iFactorClassSn
group by a.iFactorClassSn,a.iIndex
order by a.iFactorClassSn
select iFactorClassSn'+@s1+' from #t
union all
select null'+@s2+' from #t')
go

--删除测试
drop table t1,t2

/*--测试结果
iFactorClassSn 12 13 14
-------------- -------------- -------------- --------------
1 100/102.000 100/100.000 100/100.000
2 100/100.000 100/100.000 100/100.000
3 99/100.000 99/100.000 99/100.000
4 100/100.000 100/100.000 100/100.000
5 100/97.475 100/98.978 100/99.215
6 100/100.000 100/100.000 100/100.000
9 100/100.000 100/100.000 100/100.000
12 100/100.000 100/100.000 100/100.000
14 100/100.000 100/100.000 100/100.000
15 100/100.000 100/100.000 100/100.000
16 100/100.000 100/100.000 100/100.000
17 100/0.000 100/0.000 100/0.000
NULL 11.00 11.00 11.00
--*/

[zjcxc]

上面是解决:
计算各纵向比值之和，有0值不计

[Phourm]

mark