表A（基础表）
ID Name ServiceID remark
1 a 10 aa
2 b 11 bb
------------------------------
表B（基础表）
ID ServiceName
10 服务1
11 服务2
------------------------------
表C（关联记录表）
ID DoMain1（表AID） DoMain2（表AID）
1 1 2
用一条语句查出如下：
---------------------------------
Name1 Name2 ServiceName1 ServiceName2
a b 服务1 服务2
------解决思路----------------------
select a.Name as Name1,aa.Name as Name2,a.ServiceName as ServiceName1,aa.ServiceName as ServiceName2 from 表C as c,表B as b,表A as a ,表A as aa where c.DoMain1=a.ID and c.DoMain2=aa.ID and a.ServiceID=b.ID and aa.ServiceID=b.ID and c.ID=1
------解决思路----------------------
是行转列吗?
SELECT
MAX(CASE [Name] WHEN 'a' THEN [Name] END )Name1
,MAX(CASE [Name] WHEN 'b' THEN [Name] END ) Name2
,MAX(CASE [Name] WHEN 'a' THEN ServiceName END ) ServiceName1
,MAX(CASE [Name] WHEN 'b' THEN ServiceName END ) ServiceName2
FROM
CTE
------解决思路----------------------
select (select name from 表A where 表A.id=Name1) as Name1,
(select name from 表A where 表A.id=Name2) as Name2,
(select ServiceName from 表B where 表B.id=(select ServiceID from 表A where 表A.id=Name1))) as ServiceName1,
(select ServiceName from 表B where 表B.id=(select ServiceID from 表A where 表A.id=Name2) )) as ServiceName2
from 表C
------解决思路----------------------
select C.id,A.name,B.servicename,A.id as aid into #t from A,B,C where b.id=a.serviceid and (A.id=C.domain1 or A.id=C.domain2)
select * from #t
declare @s varchar(max)
select @s=ISNULL(@s+',','')+'max(case when id='''+id+''' and aid='''+aID+''' then name end) as[name'+aid+'],
max(case when id='''+ID+''' and aid='''+aid+''' then servicename end) as[servicename'+aid+']'
from #t group by aID,id
set @s='select '+@s+ ' from #t'
print @s
exec(@s)
drop table #t