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
--*/
