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

------解决方案--------------------

SQL code

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)