select a.id,b.jianli inot #t from a,b where a.id = b.id declare @sql varchar(8000) set @sql = 'select id' select @sql = @sql + ',sum(case id when '''+id+''' then jianli end) ['+id+']' from (select distinct id from #t) as a select @sql = @sql+' from #t group by id' exec(@sql)
我帮楼主讲: 表C: id jian1 jian2 jian3 1 a 1 b 1 c 2 e 2 f 2 g 3 h 3 i 4 j现在要得表D: id jian1 jian2 jian3 1 a b c 2 e f g 3 h i 4 j求得到表D的SQL语句,是吧??
----创建测试数据(注意:表B要有标识列ROWID,否则会更麻烦) declare @ta table(id int) declare @tb table(ROWID int,id int,jianli varchar(10)) /*用ROWID唯一标识行*/ declare @tc table(id int,jianli1 varchar(10),jianli2 varchar(10),jianli3 varchar(10)) insert @ta select 1 insert @tb select 1,1,'xxx' union all select 2,1,'yyy'----插入 insert @tc select a.id,b.jianli,c.jianli,d.jianli from @ta as a left join @tb as b on a.id = b.id and (select count(*) from @tb where id = b.id and ROWID < b.ROWID) = 0 left join @tb as c on a.id = c.id and (select count(*) from @tb where id = c.id and ROWID < c.ROWID) = 1 left join @tb as d on a.id = d.id and (select count(*) from @tb where id = d.id and ROWID < d.ROWID) = 2----查看 select * from @tc/*结果 id jianli jianli jianli ----------- ---------- ---------- ---------- 1 xxx yyy NULL */
就是说加入b中一个id有一条记录,那么目的表c中就只有jianli1有值
有两条记录,那么jianli1和jianli2都要有值,3个类推
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ',sum(case id when '''+id+''' then jianli end) ['+id+']'
from (select distinct id from #t) as a
select @sql = @sql+' from #t group by id'
exec(@sql)
drop table #t
c.id,jianli1,jianli2,jianli3
现在假定数据已经导入了
出现了一个问题就是
c.id,c.jianli1有值,jianli2,jianli3没有,对于一个c.id,c.jianli1有1-3个值
对于一个c.id来说,我想让它的jianli1这个字段只有1个值,因此我想把jianli1的多个值分到jianli2和jianli3中去,求个sql
表C:
id jian1 jian2 jian3
1 a
1 b
1 c
2 e
2 f
2 g
3 h
3 i
4 j现在要得表D:
id jian1 jian2 jian3
1 a b c
2 e f g
3 h i
4 j求得到表D的SQL语句,是吧??
declare @ta table(id int)
declare @tb table(ROWID int,id int,jianli varchar(10)) /*用ROWID唯一标识行*/
declare @tc table(id int,jianli1 varchar(10),jianli2 varchar(10),jianli3 varchar(10))
insert @ta select 1
insert @tb
select 1,1,'xxx' union all
select 2,1,'yyy'----插入
insert @tc
select a.id,b.jianli,c.jianli,d.jianli from @ta as a
left join @tb as b on a.id = b.id and (select count(*) from @tb where id = b.id and ROWID < b.ROWID) = 0
left join @tb as c on a.id = c.id and (select count(*) from @tb where id = c.id and ROWID < c.ROWID) = 1
left join @tb as d on a.id = d.id and (select count(*) from @tb where id = d.id and ROWID < d.ROWID) = 2----查看
select * from @tc/*结果
id jianli jianli jianli
----------- ---------- ---------- ----------
1 xxx yyy NULL
*/