用一条sql语句解决里面的问题
表company

cID CompanyName

1 公司1

2 公司名称



表companyAdd

dID cID Add

1 1 add1

2 1 add2

3 1 add3

4 2 add4

... ... ...

用一条sql语句怎么得出如下表格

CompanyName Add1 Add2 Add3

公司1 add1 add2 add3

公司2 add4 ... ...


[解决办法]
需要使用函数
[解决办法]
--如果add不定的,需要句
--境:

create table company(cID int,companyname varchar(30))
insert into company
select 1, '公司1 ' union all
select 2, '公司2 '

create table companyAdd(dID int,cID int,[Add] varchar(20))
insert into companyAdd
select 1,1, 'add1 ' union all
select 2,1, 'add2 ' union all
select 3,1, 'add3 ' union all
select 4,2, 'add4 '


declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',max(case when id= '+ltrim(id)+ ' then [Add] end) as [add '+ltrim(id)+ '] '
from
(select id=(select count(1) from companyAdd where dID <=A.dID and cID=A.cID),B.companyName,A.[Add]
from companyAdd A,company B where A.cId=B.cID) T
group by id
select @sql= 'select companyName '+@sql+ ' from (select id=(select count(1) from companyAdd where dID <=A.dID and cID=A.cID),B.companyName,A.[Add]
from companyAdd A,company B where A.cId=B.cID) T group by companyName '

exec(@sql)
/*
companyName add1 add2 add3
----------------------------------------------
公司1 add1 add2 add3
公司2 add4 NULL NULL
*/
drop table companyAdd,company



[解决办法]
固定的可以这样

select a.CompanyName,
Add1=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 0 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID) else ' ' end,
Add2=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 1 then (select top 1 [Add] from (select top 2 * from companyAdd b where b.cID=a.cID order by b.dID)t order by dID desc) else ' ' end,
Add3=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 2 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID desc) else ' ' end
from company a
------解决方案--------------------

drop table company,companyAdd
go
create table company(cID int,CompanyName varchar(20))
insert into company
select 1, '公司1 '
union all select 2, '公司2 '

create table companyAdd(dID int,cID int,[Add] varchar(20))
insert into companyAdd
select 1,1, 'add1 '
union all select 2,1, 'add2 '
union all select 3,1, 'add3 '
union all select 4,2, 'add4 '

select a.CompanyName,
Add1=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 0 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID) else ' ' end,
Add2=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 1 then (select top 1 [Add] from (select top 2 * from companyAdd b where b.cID=a.cID order by b.dID)t order by dID desc) else ' ' end,
Add3=case when (select count(*) from companyAdd where companyAdd.cID=a.cID)> 2 then (select top 1 b.[Add] from companyAdd b where b.cID=a.cID order by b.dID desc) else ' ' end
from company a

/*
CompanyName Add1 Add2 Add3
-------------------- -------------------- -------------------- --------------------
公司1 add1 add2 add3
公司2 add4

（所影响的行数为 2 行）
*/
[解决办法]
if object_id( 'pubs..company ') is not null
drop table company
go

create table company(cID int,CompanyName varchar(10))
insert into company(cID,CompanyName) values(1, '公司1 ')
insert into company(cID,CompanyName) values(2, '公司2 ')
go

if object_id( 'pubs..companyAdd ') is not null
drop table companyAdd
go

create table companyAdd(dID int,cID int,[Add] varchar(10))
insert into companyAdd(dID,cID,[Add]) values(1, 1, 'add1 ')
insert into companyAdd(dID,cID,[Add]) values(2, 1, 'add2 ')
insert into companyAdd(dID,cID,[Add]) values(3, 1, 'add3 ')
insert into companyAdd(dID,cID,[Add]) values(4, 2, 'add4 ')
go

declare @sql varchar(8000)
set @sql = 'select CompanyName '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then [add] else ' ' ' ' end) [add ' + cast(px as varchar) + '] '
from (select distinct px from (select CompanyName , t.px , t.[add] from company , (select px=(select count(1) from companyAdd where cID=a.cID and dID <a.dID)+1 , * from companyAdd a) t where company.cid = t.cid) m) as a
set @sql = @sql + ' from (select CompanyName , t.px , t.[add] from company , (select px=(select count(1) from companyAdd where cID=a.cID and dID <a.dID)+1 , * from companyAdd a) t where company.cid = t.cid) m group by CompanyName '
exec(@sql)

drop table companyAdd
drop table company

/*
CompanyName add1 add2 add3
----------- ---------- ---------- ----------
公司1 add1 add2 add3
公司2 add4
*/

[解决办法]
--如果add是定的,可以直接一句SQL

select
companyname,
max(case when id=1 then [add] end) as add1,
max(case when id=2 then [add] end) as add2,
max(case when id=3 then [add] end) as add3
from
(select id=(select count(1) from companyAdd where dID <=A.dID and cID=A.cID),B.companyName,A.[Add]
from companyAdd A,company B where A.cId=B.cID) T

group by companyname

/*
companyname add1 add2 add3
------------------------------ -------------------- -------------------- ------------
公司1 add1 add2 add3
公司2 add4 NULL NULL
*/