如何1对多的两个表，合成多列的一个表

Question

declare @select varchar(1000)
set @select=''
select @select=@select+','+'(case when city='+cast(city as varchar)+' then size end) as ' + city  from tb2
set @select='select province ' +@select+' from tb2 group by province '
exec(@select)

Answer 1

Create  table  tb1(id int,province  varchar(10) )
insert  into  tb1
Select  1,    '北京'   union all
Select  2 ,   'Guagn'   union all
Select  3 ,   '上海'
---------------------建表2-----------------------------------
Create  Table  tb2(id int, province  varchar(10),  city varchar(10), size  int)
Insert  Into  tb2
Select  1,    '北京'  ,    'aa',         50  union  all
Select  2 ,   '北京'  ,    'bb',         100  union  all
Select  3 ,   '北京'  ,    'cc' ,        200  union  all
Select  4 ,   '北京'  ,    'dd' ,        50  union  all
Select  5 ,   'Guagn' ,     'ee'  ,       100   union  all
Select  6 ,   'Guagn' ,     'ff'   ,      100  union  all
Select  7 ,   '上海' ,     'gg'  ,       100
-------------------------實現語句-------------------------------------------------
select  tb1.id,tb1.province,city1=k.city, size1=k.size, city2=isnull(h.city,''),
 size2=h.size,city3=g.city, size3=g.size
 from  tb1  left  join 
(select   *  from   tb2 a  where  id in(select  top 1 id  from tb2 where  province=a.province)  )  k
 on  tb1.province=k.province
 left  join  
(select  *  from 
(select   *  from   tb2 a  where  id in(select  top 2 id  from tb2 where  province=a.province)  )  b
where  id not in (select  top 1 id  from tb2 where  province=b.province) )  h
 on   k.province=h.province   left join 
(select  *  from 
(select   *  from   tb2 a  where  id in(select  top 3 id  from tb2 where  province=a.province)  )  b
where  id not in (select  top 2 id  from tb2 where  province=b.province)  )  g
on  k.province=g.province
--------------------輸出------------------------------------------
id   province   city1  size1  city2 size2  city3 size3
1    北京        aa     50     bb    100    cc    200
2    广东        ee     100    ff    100      
3    上海        gg     100

Answer 2

好象很复杂，还有没有简单的方法了
可不可以用select top 3 * from tb2之类的形式组合

Answer 3

Create  table  tb1(id int,province  varchar(10) )
insert  into  tb1
Select  1,    '北京'   union all
Select  2 ,   'Guagn'   union all
Select  3 ,   '上海'
---------------------建表2-----------------------------------
Create  Table  tb2(id int, province  varchar(10),  city varchar(10), size  int)
Insert  Into  tb2
Select  1,    '北京'  ,    'aa',         50  union  all
Select  2 ,   '北京'  ,    'bb',         100  union  all
Select  3 ,   '北京'  ,    'cc' ,        200  union  all
Select  4 ,   '北京'  ,    'dd' ,        50  union  all
Select  5 ,   'Guagn' ,     'ee'  ,       100   union  all
Select  6 ,   'Guagn' ,     'ff'   ,      100  union  all
Select  7 ,   '上海' ,     'gg'  ,       100
-------------------------實現語句-------------------------------------------------select a.id,a.province
,city1=isnull(max(case gid when 1 then b.city end),'')
,size1=isnull(max(case gid when 1 then cast(b.size as varchar) end),'')
,city2=isnull(max(case gid when 2 then b.city end),'')
,size2=isnull(max(case gid when 2 then cast(b.size as varchar) end),'')
,city3=isnull(max(case gid when 3 then b.city end),'')
,size3=isnull(max(case gid when 3 then cast(b.size as varchar) end),'')
from tb1 a
left join(
select province,city,size
,gid=(select count(*) from tb2 where province=bb.province and id<=bb.id)
from tb2 bb
where id in(select top 3 id from tb2 where province=bb.province)
)b on a.province=b.province
group by a.id,a.province
order by a.id----删除测试--
drop table tb1,tb2