关于交叉表的最后一个问题的延续

Question

进一步延续：
如果这里的iIndex int字段换成sDescribe varchar(20)字段，该如何实现交叉表。就是将iIndex int看成Varchar类型，如何生成下表！
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    …
...

Answer 1

--1.将iIndex int看成Varchar类型的处理方法不变--2.下面是各纵向相乘后的结果--测试数据
create table t1(sGroundNo char(5),iIndex varchar(20),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,17create 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),@s3 varchar(8000),@s4 varchar(8000)
,@s5 varchar(8000)
select @s = '',@s1='',@s2='',@s3='',@s4='',@s5=''
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+' decimal(30,4)'
,@s3=@s3+',@'+sExampleNo+'=1'
,@s4=@s4+',@'+sExampleNo+'=@'+sExampleNo+'*case when ['+sExampleNo
+']=0 or iIndex=0 then 1 else iIndex/['+sExampleNo+'] end'
,@s5=@s5+',['+sExampleNo+']=cast(@'+sExampleNo+' as varchar)'
from (select distinct sExampleNo=cast(sExampleNo as varchar) from t2) as aselect @s2='declare '+substring(@s2,2,8000)
,@s3='select '++substring(@s3,2,8000)
,@s4='select '+substring(@s4,2,8000)+' from #t'
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
'+@s2+'
'+@s3+'
'+@s4+'
select iFactorClassSn'+@s1+' from #t
union all
select null'+@s5)
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           0.9957         1.0002         0.9978
--*/

Answer 2

zjcxc(邹建)，老大，将iIndex int看成Varchar类型的处理方法不变，是否表示我将iIndex字段直接换成sDescribe字段，t1,t2表中假如都有sDescribe字段！但程序报
“服务器: 消息 306，级别 16，状态 2，行 1
不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。”请老大指点！

Answer 3

你的sDescribe字段是text、ntext 和 image ?那当然不行.我是说int类型变为varchar类型一样.

Answer 4

我的sDescribe是varchar(20),不能转换了吗？

Answer 5

一个表中的sDescribe为Text，不好意思！！！我该如何修改下面的语句，将iIndex字段换成sDescribe字段！实现如上效果！～请指点！
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 aselect @sql = @sql+ ' from t2 join t1 on t2.iFactorClassSn = t1.iFactorClassSn group by t2.iFactorClassSn,t1.iIndex
order by t2.iFactorClassSn'exec(@sql)

Answer 6

可以啊.你的表结构贴出来?怎么会出这个提示:不能比较或排序 text、ntext 和 image 数据类型

Answer 7

表结构跟上面的一样,也就是将上面的iIndex int换成了sDescribe varchar(20),借用你的整理修改如下
create table t1(sGroundNo char(5),sDescribe varchar(20),iFactorClassSn int)
insert t1 select 'zd001','A1',1
union all select 'zd001','A51',2
union all select 'zd001','A21',3
union all select 'zd001','A31',4
create table t2(sGroundNo char(5),sExampleNo int,sDescribe varchar(20),iFactorClassSn int)
insert t2 select 'zd001',12,'aa1',1
union all select 'zd001',12,'aa2',2
union all select 'zd001',12,'aa3',3
union all select 'zd001',12,'aa4',4
union all select 'zd001',13,null,1
union all select 'zd001',13,null,2
union all select 'zd001',13,'bb',3
union all select 'zd001',13,null,4
union all select 'zd001',14,'cc2',1
union all select 'zd001',14,null,2
union all select 'zd001',14,'cc5',3
union all select 'zd001',14,null,4生成：
iFactorClassSn    zd001   12    13     14    …
1                 A1      aa1   null   cc2    …
2                 A51     aa2   null   null   …
3                 A21     aa3   bb     cc5    …
4                 A31     aa4   null   null   …

Answer 8

--测试数据
create table t1(sGroundNo char(5),sDescribe varchar(20),iFactorClassSn int)
insert t1 select 'zd001','A1',1
union all select 'zd001','A51',2
union all select 'zd001','A21',3
union all select 'zd001','A31',4create table t2(sGroundNo char(5),sExampleNo int,sDescribe varchar(20),iFactorClassSn int)
insert t2 select 'zd001',12,'aa1',1
union all select 'zd001',12,'aa2',2
union all select 'zd001',12,'aa3',3
union all select 'zd001',12,'aa4',4
union all select 'zd001',13,null,1
union all select 'zd001',13,null,2
union all select 'zd001',13,'bb',3
union all select 'zd001',13,null,4
union all select 'zd001',14,'cc2',1
union all select 'zd001',14,null,2
union all select 'zd001',14,'cc5',3
union all select 'zd001',14,null,4--数据处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+aa+']=max(case sExampleNo when '''
+aa+''' then b.sDescribe end)'
from(select distinct aa=cast(sExampleNo as varchar) from t2) a
exec('select a.iFactorClassSn,a.sDescribe'+@s+'
from t1 a join t2 b on a.iFactorClassSn=b.iFactorClassSn
group by a.iFactorClassSn,a.sDescribe
order by a.iFactorClassSn,a.sDescribe')go
--删除测试环境
drop table t1,t2/*--测试结果
iFactorClassSn sDescribe   12          13       14
-------------- ----------- ----------- -------- -------
1              A1          aa1         NULL     cc2
2              A51         aa2         NULL     NULL
3              A21         aa3         bb       cc5
4              A31         aa4         NULL     NULL--*/

Answer 9

非常感谢 zjcxc(邹建) ！！！