日期:2014-05-18 浏览次数:20488 次
create table t1(id int,c1 varchar(100)) go insert t1 select 1,'111' union select 2,'222' create table t2(c2 varchar(100)) go insert t2 select 'code' union select 'name' create table t3(id int,c3 varchar(100), val varchar(100)) go insert t3 select 1,'code','aaaa' union select 1,'name','bbbb' union select 2,'code','ab' union select 2,'name','aabb' go select a.ID,col = c1+(select ','+val from t3 where ID = a.id for XML path('')) from t3 a join t1 b on a.id = b.id group by a.id,b.c1 order by a.id go drop table t1,t2,t3
------解决方案--------------------
create table 表1 (id int, field1 varchar(6)) insert into 表1 select 1,'111' union all select 2,'222' create table 表2 (field2 varchar(6)) insert into 表2 select 'code' union all select 'name' create table 表3 (id int, field3 varchar(6), value varchar(6)) insert into 表3 select 1, 'code', 'aaaa' union all select 1, 'name', 'bbbb' union all select 2, 'code', 'ab' union all select 2, 'name', 'aabb' select a.id,a.field1,c.code,c.name from 表1 a inner join (select id,code,name from 表3 t pivot(max(value) for field3 in(code,name)) v) c on a.id=c.id id field1 code name ----------- ------ ------ ------ 1 111 aaaa bbbb 2 222 ab aabb (2 row(s) affected)