怎样把数据库的行转成列

 

有如下格式的表

Company Name        Exchange   Listing Countries         Business Country             

BYD                               OTC PK      USA                                CHN

BYD                              SZSE           CHN                               CHN

BYD                               XTER          GEM                                CHN

BYD                              HKSE           HK                                  CHN

怎么样转成一列，后面带listing countries呢， 脚本如下 (最关键是粗体部分)

 

 select CompanyId,
  CountryId_ = stuff((select ','+ CountryId from #Company where CompanyId=t.CompanyId for xml path('')),1,1,'')
 from #Company t
 group by CompanyId