--测试:
create table t1(zt char(2),ddh char(4),lb char(1))
insert into t1 select 'AA','1111','1'
insert into t1 select 'BB','1111','2'
insert into t1 select 'CC','1111','3'
insert into t1 select 'CC','2222','2'
insert into t1 select 'BB','2222','1'
insert into t1 select 'EE','3333','3'godeclare @sql varchar(8000)
set @sql = 'select ddh'
select @sql = @sql + ',max(case lb when '''+lb+''' then zt end) [zt'+lb+']'
from (select distinct lb from t1) as a
select @sql = @sql+' from t1 group by ddh'
exec(@sql)
/*
ddh zt1 zt2 zt3
---- ---- ---- ----
1111 AA BB CC
2222 BB CC NULL
3333 NULL NULL EE
*/
create table t1(zt char(2),ddh char(4),lb char(1))
insert into t1 select 'AA','1111','1'
insert into t1 select 'BB','1111','2'
insert into t1 select 'CC','1111','3'
insert into t1 select 'CC','2222','2'
insert into t1 select 'BB','2222','1'
insert into t1 select 'EE','3333','3'godeclare @sql varchar(8000)
set @sql = 'select ddh'
select @sql = @sql + ',max(case lb when '''+lb+''' then zt end) [zt'+lb+']'
from (select distinct lb from t1) as a
select @sql = @sql+' from t1 group by ddh'
exec(@sql)
/*
ddh zt1 zt2 zt3
---- ---- ---- ----
1111 AA BB CC
2222 BB CC NULL
3333 NULL NULL EE
*/
create table t1(zt char(2),ddh char(4),lb char(1))
insert into t1 select 'AA','1111','1'
insert into t1 select 'BB','1111','2'
insert into t1 select 'CC','1111','3'
insert into t1 select 'CC','2222','2'
insert into t1 select 'BB','2222','1'
insert into t1 select 'EE','2222','3'godeclare @sql varchar(8000)
set @sql = 'select ddh'
select @sql = @sql + ',max(case lb when '''+lb+''' then zt end) [zt'+lb+']'
from (select distinct lb from t1) as a
select @sql = @sql+' from t1 group by ddh'
exec(@sql)
/*
ddh zt1 zt2 zt3
---- ---- ---- ----
1111 AA BB CC
2222 BB CC EE
*/
ddh zc1 zc2 zc3
---- ---- ---- ----
1111 AA BB CC
2222 BB CC NULL
3333 NULL NULL EE
--
--测试
create table #aa(zt varchar(2),ddh varchar(4),lb int)
insert #aa select 'AA', '1111', 1
union all select 'BB' , '1111' , 2
union all select 'CC' , '1111' , 3
union all select 'CC' ,'2222' , 2
union all select 'BB' , '2222' , 1
union all select 'EE' , '3333' , 3
Go
declare @sql varchar(8000)
set @sql = 'select ddh'
select @sql = @sql + ',max(case lb when '''+cast(lb as varchar(2))+''' then zt end) ['+'zc'+cast(lb as varchar(2))+']'
from (select distinct lb from #aa) as a
select @sql
select @sql = @sql+' from #aa group by ddh'
exec (@sql)
drop table #aa
set @sql = 'select ddh'
select @sql = @sql + ',max(case lb when '''+cast(lb as varchar(2))+''' then zt else '''' end) ['+'zc'+cast(lb as varchar(2))+']'
from (select distinct lb from #aa) as a
select @sql
select @sql = @sql+' from #aa group by ddh'
exec (@sql)
--结果:
ddh zc1 zc2 zc3
---- ---- ---- ----
1111 AA BB CC
2222 BB CC
3333 EE