写一个sql语句，比较复杂，自信的高手请进

[wangjiamin]

b是客户表，a是联系人表，一个客户对应多个联系人。
输出数据

客户 联系人1 联系人2 联系人3。。。联系人n

要求：一个客户只能占一行，每一个联系人 占一列。 后面的空着。n标识最多联系人的客户的联系人数

表结构如下：


CREATE TABLE [b] (
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customername] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cphone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dq] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO




CREATE TABLE [a] (
[nameid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[name1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

[zjcxc]

declare @s varchar(8000),@i int
select @s='',@i=max(rw)
from(select rw=count(*) from a group by customerid)a
while @i>0
select @s=',[联系人'+cast(@i as varchar)
+']=sum(case sid when '+cast(@i as varchar)
+' then 1 else 0 end)'+@s
,@i=@i-1
exec('select customername'+@s+'
from(
select b.customername,sid=(
select sum(1) from a
where customerid=b.customerid
and nameid<=a.nameid)
from a,b
where a.customerid=b.customerid
)a group by customername
')

[yesterday2000]

高

[zjcxc]

--上面搞错了,应该是显示名称,不是计数

declare @s varchar(8000),@i int
select @s='',@i=max(rw)
from(select rw=count(*) from a group by customerid)a
while @i>0
select @s=',[联系人'+cast(@i as varchar)
+']=max(case sid when '+cast(@i as varchar)
+' then name1 else '''' end)'+@s
,@i=@i-1
exec('select customername'+@s+'
from(
select b.customername,a.name1,sid=(
select sum(1) from a aa
where customerid=b.customerid
and nameid<=a.nameid)
from a,b
where a.customerid=b.customerid
)a group by customername
')

[zjcxc]

CREATE TABLE [b] (
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customername] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cphone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dq] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
insert b select '001','部门A','11','11'
union all select '002','部门B','22','22'
GO

CREATE TABLE [a] (
[nameid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[name1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mail] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[customerid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
insert a select '001','A1','11','11','001'
union all select '002','A2','22','22','002'
union all select '003','A3','22','22','002'
union all select '004','A4','22','22','002'
union all select '005','A5','22','22','001'
GO

--查询
declare @s varchar(8000),@i int
select @s='',@i=max(rw)
from(select rw=count(*) from a group by customerid)a
while @i>0
select @s=',[联系人'+cast(@i as varchar)
+']=max(case sid when '+cast(@i as varchar)
+' then name1 else '''' end)'+@s
,@i=@i-1
exec('select customername'+@s+'
from(
select b.customername,a.name1,sid=(
select sum(1) from a aa
where customerid=b.customerid
and nameid<=a.nameid)
from a,b
where a.customerid=b.customerid
)a group by customername
')
go

--删除测试
drop table a,b

/*--测试结果

customername 联系人1 联系人2 联系人3
------------- --------- ---------- ----------
部门A A1 A5
部门B A2 A3 A4
--*/

[wangjiamin]

to: zjcxc(邹建)
确实不错，高的很。pfpf

[zonelive]

强，不献丑了