这样的SQL怎么实现？

Question

表1为客户基本信息表
ID  companyname  addr    tel   fax
1   北京A         xxx   1234   5678......
表2为客户订单表
OrderID  CID  ProductType  Product   Quantity
1         1     电子        MP3          22
2         1     电子        电脑         11
3         1     生活        脸盆         11
4         1     生活        香皂         44     ........现在想通过一条SQL语句得到这样的表ID  companyname  addr    tel   fax            电子              生活
1   北京A         xxx   1234   5678      MP3X22，电脑X11   脸盆X11，香皂X44.......................

Answer 1

create table a
(ID int, companyname char(10), addr char(10),   tel int  ,fax int)
insert a
select 1   ,'北京A'         ,'xxx'   ,1234   ,5678create table b
(OrderID int, CID int, ProductType char(10), Product char(10),  Quantity char(10))
insert b
select 1         ,1     ,'电子'        ,'MP3'          ,'22'
union all
select 2         ,1     ,'电子'        ,'电脑'         ,'11'
union all
select 3         ,1     ,'生活'        ,'脸盆'         ,'11'
union all
select 4         ,1     ,'生活'        ,'香皂'         ,'44'
create function cx(@producttype varchar(8000))
returns varchar(8000)
as 
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+product+'*'+quantity from b where producttype=@producttype
set @sql=stuff(@sql,1,1,'')
return @sql
end
goselect id,companyname,addr,tel,fax,cid
,电子=max(case when producttype='电子' then aa end)
,生活=max(case when producttype='生活' then aa end)      
from a join (select cid,producttype,aa=dbo.cx(producttype) from b group by cid,producttype)c on a.id=c.cid
group by id,companyname,addr,tel,fax,ciddrop function cx
drop table a
drop table bid          companyname addr       tel         fax         cid         电子                                                                                                                                                                                                                                                               生活                                                                                                                                                                                                                                                               
----------- ----------- ---------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           北京A         xxx        1234        5678        1           MP3       *22        ,电脑      *11                                                                                                                                                                                                                                脸盆      *11        ,香皂      *44        （所影响的行数为 1 行）

Answer 2

create function dbo.fc_str(@no varchar(100),@ProductType varchar(100))
returns varchar(100)
as
begin
 declare @sql varchar(1000)
 set @sql=''
 select @sql=@sql+','+ Product+'X'+cast(Quantity as varchar)  from 客户订单表 where CID=@no and ProductType =@ProductType  
 return stuff(@sql,1,1,'')
end
go declare @sql varchar(1000)
 select @sql='select distinct * from 
(select  CID'
select @Sql=@sql+','+ProductType+'=case ProductType when '''+ProductType+''' then '''+dbo.fc_str(cid,ProductType)+''' else '''+dbo.fc_str(cid,ProductType)+''' end' 
from
(select a.Cid,a.ProductType,x=Product+'X'+cast(Quantity as varchar) from 客户订单表 b
inner join 
(select distinct CID,ProductType from 客户订单表) a
on a.cid=b.cid and a.ProductType=b.ProductType)a
group by Cid,ProductType
select @Sql=@sql+'
from 客户订单表 group by CID,ProductType)a'--print @sqlexec(@Sql)--得到下面的结果，其他的自己写吧
1 MP3X22,电脑X11 脸盆X11,香皂X44

Answer 3

create table a
(ID int, companyname char(10), addr char(10),   tel int  ,fax int)create table b
(OrderID int, CID int, ProductType char(10), Product char(10),  Quantity char(10))create function cx(@producttype varchar(8000))
returns varchar(8000)
as 
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+product+'*'+quantity from b where producttype=@producttype
set @sql=stuff(@sql,1,1,'')
return @sql
endselect id,companyname,addr,tel,fax,cid
,电子=max(case when producttype='电子' then aa end)
,生活=max(case when producttype='生活' then aa end)      
from a join (select cid,producttype,aa=dbo.cx(producttype) from b group by cid,producttype)c on a.id=c.cid
group by id,companyname,addr,tel,fax,cid--收藏