如将以下表: -------------------------------- col1 col2 col3 1 a 5 1 b 6 2 a 4 2 b 5 2 c 6转为如下格式: -------------------------------- col1 a b c 1 5 6 2 4 5 6 用动态SQL的处理如下: -------------------------------- declare @s varchar(8000) set @s='' select @s=@s+',['+col2+']=max(case col2 when '''+col2+''' then col3 end)' from 表 group by col2 order by col2 set @s='select col1'+@s+' from 表 group by col1' exec(@s)
变通一下,将字典表的结构建为如下: ----------------------------------------------------------------------------- create table t1(id int,colid int,b varchar(10)) insert into t1 select 1,2,'水费' insert into t1 select 1,3,'房补' insert into t1 select 1,4,'水电' insert into t1 select 1,5,'奶粉费' insert into t1 select 1,6,'清凉费' insert into t1 select 1,7,'学费' insert into t1 select 1,8,'书费'create table t2(id int,a1 varchar(10),a2 varchar(10),a3 varchar(10),a4 varchar(10), a5 varchar(10),a6 varchar(10),a7 varchar(10)) insert into t2 select 1,300,444,333,2233,222,111,334 insert into t2 select 2,323,324,324,432 ,234,443,233 --执行交叉表查询 declare @s varchar(8000) set @s='' select @s=@s+','+a.name+case a.colid when 1 then '' else ' as '+b.b end from (select * from syscolumns where id=object_id('t2')) a left join (select * from t1 where id=1) b on a.colid = b.colid order by a.colidset @s = 'select '+stuff(@s,1,1,'')+' from t2'print @s exec(@s)--输出结果 /* id 水费 房补 水电 奶粉费 清凉费 学费 书费 -------------------------------------------------------------------------------------------------------- 1 300 444 333 2233 222 111 334 2 323 324 324 432 234 443 233 */ --删除测试数据 drop table t1,t2
1 300 444 333 2233 222 111 334
2 323 324 324 432 234 443 233让b1对应a1,b2对应a2,b3对应a3......
使用动态SQL执行交叉表查询怎么来做,贴个句子看看?
--------------------------------
col1 col2 col3
1 a 5
1 b 6
2 a 4
2 b 5
2 c 6转为如下格式:
--------------------------------
col1 a b c
1 5 6
2 4 5 6
用动态SQL的处理如下:
--------------------------------
declare @s varchar(8000)
set @s=''
select @s=@s+',['+col2+']=max(case col2 when '''+col2+''' then col3 end)' from 表 group by col2 order by col2
set @s='select col1'+@s+' from 表 group by col1'
exec(@s)
-----------------------------------------------------------------------------
create table t1(id int,colid int,b varchar(10))
insert into t1 select 1,2,'水费'
insert into t1 select 1,3,'房补'
insert into t1 select 1,4,'水电'
insert into t1 select 1,5,'奶粉费'
insert into t1 select 1,6,'清凉费'
insert into t1 select 1,7,'学费'
insert into t1 select 1,8,'书费'create table t2(id int,a1 varchar(10),a2 varchar(10),a3 varchar(10),a4 varchar(10),
a5 varchar(10),a6 varchar(10),a7 varchar(10))
insert into t2 select 1,300,444,333,2233,222,111,334
insert into t2 select 2,323,324,324,432 ,234,443,233
--执行交叉表查询
declare @s varchar(8000)
set @s=''
select
@s=@s+','+a.name+case a.colid when 1 then '' else ' as '+b.b end
from
(select * from syscolumns where id=object_id('t2')) a
left join
(select * from t1 where id=1) b
on
a.colid = b.colid
order by
a.colidset @s = 'select '+stuff(@s,1,1,'')+' from t2'print @s
exec(@s)--输出结果
/*
id 水费 房补 水电 奶粉费 清凉费 学费 书费
--------------------------------------------------------------------------------------------------------
1 300 444 333 2233 222 111 334
2 323 324 324 432 234 443 233
*/
--删除测试数据
drop table t1,t2