zjcxc(邹建)上午的语句有变动

[ADWARDS00]

具体请看http://expert.csdn.net/Expert/topic/2604/2604582.xml?temp=.9685022
上午字段2的数据是纯数字的,但是现在出现类似 1200X300 这样的数据,储存过程就不能执行了

[zjcxc]

我再看看.

[zjcxc]

--改成这样就行了.:

declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '''+b+''' THEN cast(c as varchar) else '''' END)'
from(select distinct b from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')

[zjcxc]

--下面是测试

--测试数据
create table 表(a varchar(3),b varchar(20),c int)
insert 表
select 'aaa','1001 x 100',1465
union all select 'bbb','1002 x 300',1124
union all select 'ccc','1003 x 100',1257
union all select 'ddd','1004',4855
union all select 'aaa','1004',1144
go

--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '''+b+''' THEN cast(c as varchar) else '''' END)'
from(select distinct b from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')
go

--删除测试表
DROP TABLE 表

/*--测试结果

a 1001 x 100 1002 x 300 1003 x 100 1004
---- ---------------- ---------------- ---------------- ----------------
aaa 1465 1144
bbb 1124
ccc 1257
ddd 4855

--*/

[ADWARDS00]

晕~强,能不能给解释一下啊
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '''+b+''' THEN cast(c as varchar) else '''' END)'
from(select distinct b from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')