select t1.id,t1.body,t2.body b_body,t3.body b_body1
from a表 t1,
(select * from b where type=0) t2,
(select * from b where type=1) t3
where t1.id=t2.id and t1.id=t3.id
from a表 t1,
(select * from b where type=0) t2,
(select * from b where type=1) t3
where t1.id=t2.id and t1.id=t3.id
from tablea a left join tableb on a.id=b.id
select a.id,a.body as b_body0,b.body as b_body1
from tablea a left join tableb b on a.id=b.id
(select body from b where id=a.id and type=0) b_body0,
(select body from b where id=a.id and type=1) b_body1
from a
max(case when b.type=1 then b.body end) from
ab a,cd b where a.id=b.id group by b.id
max(case when b.type=1 then b.body end) as b_body1 from a,b
where a.id=b.id group by b.id
insert into a (id,body) values (2,'bbb')select * from a create table b (id int,type bit ,body varchar(10))insert into b (id,type,body) values(1,0,'aaa')insert into b (id,type,body) values(1,1,'AAA')insert into b (id,type,body) values(2,0,'bbb')insert into b (id,type,body) values(2,1,'BBB')select * from bselect a.id ,a.body,b_body0=(select b.body from b where b.id=a.id and b.type=0),b_body1=(select b.body from b where b.id=a.id and b.type=1) from a
from a, (select * from b where b.type=0) b1,
(select * from b were b.type=1) b2
where a.id=b.id
SELECT a.id AS id,a.body,
b0.body AS b_body0,
b1.body AS b_body1
FROM a,
(SELECT id,body
FROM b
WHERE b.type = 0) b0,
(SELECT id,body
FROM b
WHERE b.type = 1) b1
WHERE b0.id = a.id
AND b1.id = a.id
from a
left join
(select id ,body from b where type='0') c on a.id=c.id
left join
(select id ,body from b where type='1')
d on a.id=d.id
insert into 表
select '1','11','56'
union all
select '1','22','fgh'
union all
select '2','11','7'
union all
select '2','22','m'godeclare @sql varchar(8000)
select @sql='select id '
select @sql=@sql + ',max(case name1 when '''+ rtrim(name1) +''' then
rtrim (value1) else
'''' end) ['+rtrim(name1)+']'
from 表
group by name1
select @SQL=@SQL+' from 表 group by Id order by id'
exec(@sql)drop table 表