百分求SQL语句，不知道是联合查询还是行专列的问题，请高手
问题解决立即揭帖，三天内还没有解决，分给顶贴的朋友。
问题描述如下：
表A （IDA, NameA) 其中IDA为主键
IDA NameA
1 namea1
2 namea2
3 namea3
4 namea4
表B (IDB,NameB) 其中IDB为主键
IDB NameB
1 nameb1
2 nameb2
3 nameb3
表C（IDC,IDA,IDB，State) 其中IDC为主键,IDA,IDB 为外键。State为字符型(取值范围为A,B,C)
IDC IDA IDB State
1 1 1 A
2 2 1 A
3 3 1 C
4 1 2 B
5 2 2 A
6 3 2 B
7 1 3 A
8 2 3 C
9 3 3 A
10 4 3 B

希望查询到的结果为：
IDB NameB namea1 namea2 namea3 namea4
1 nameb1 A A C ' '
2 nameb2 B A B ' '
3 nameb3 A C A B

[解决办法]
ding
[解决办法]
关注
[解决办法]
--先给个静态的SQL
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(IDA int,NameA varchar(10))
insert into A(IDA,NameA) values(1, 'namea1 ')
insert into A(IDA,NameA) values(2, 'namea2 ')
insert into A(IDA,NameA) values(3, 'namea3 ')
insert into A(IDA,NameA) values(4, 'namea4 ')
go

if object_id( 'pubs..B ') is not null
drop table B
go
create table B(IDB int,NameB varchar(10))
insert into B(IDB,NameB) values(1, 'nameb1 ')
insert into B(IDB,NameB) values(2, 'nameb2 ')
insert into B(IDB,NameB) values(3, 'nameb3 ')
go

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

create table C(IDC int,IDA int,IDB int,State varchar(10))
insert into C(IDC,IDA,IDB,State) values(1, 1, 1, 'A ')
insert into C(IDC,IDA,IDB,State) values(2, 2, 1, 'A ')

insert into C(IDC,IDA,IDB,State) values(3, 3, 1, 'C ')
insert into C(IDC,IDA,IDB,State) values(4, 1, 2, 'B ')
insert into C(IDC,IDA,IDB,State) values(5, 2, 2, 'A ')
insert into C(IDC,IDA,IDB,State) values(6, 3, 2, 'B ')
insert into C(IDC,IDA,IDB,State) values(7, 1, 3, 'A ')
insert into C(IDC,IDA,IDB,State) values(8, 2, 3, 'C ')
insert into C(IDC,IDA,IDB,State) values(9, 3, 3, 'A ')
insert into C(IDC,IDA,IDB,State) values(10, 4, 3, 'B ')
go

select IDB , NameB,
max(case when NameA = 'namea1 ' then State else ' ' end) as namea1,
max(case when NameA = 'namea2 ' then State else ' ' end) as namea2,
max(case when NameA = 'namea3 ' then State else ' ' end) as namea3,
max(case when NameA = 'namea4 ' then State else ' ' end) as namea4
from
(
select A.NameA,B.IDB,B.NameB,C.State from A,B,C
where C.IDA = A.IDA and C.IDB = B.IDB
) t
group by IDB , NameB

drop table A,B,C
/*
IDB NameB namea1 namea2 namea3 namea4
----------- ---------- ---------- ---------- ---------- ----------
1 nameb1 A A C
2 nameb2 B A B
3 nameb3 A C A B

（所影响的行数为 3 行）
*/
[解决办法]
楼上正解
[解决办法]
create table A(IDA int,NameA varchar(10))
insert into A(IDA,NameA) values(1, 'namea1 ')
insert into A(IDA,NameA) values(2, 'namea2 ')
insert into A(IDA,NameA) values(3, 'namea3 ')
insert into A(IDA,NameA) values(4, 'namea4 ')

create table B(IDB int,NameB varchar(10))
insert into B(IDB,NameB) values(1, 'nameb1 ')
insert into B(IDB,NameB) values(2, 'nameb2 ')
insert into B(IDB,NameB) values(3, 'nameb3 ')

create table C(IDC int,IDA int,IDB int,State varchar(10))
insert into C(IDC,IDA,IDB,State) values(1, 1, 1, 'A ')
insert into C(IDC,IDA,IDB,State) values(2, 2, 1, 'A ')
insert into C(IDC,IDA,IDB,State) values(3, 3, 1, 'C ')
insert into C(IDC,IDA,IDB,State) values(4, 1, 2, 'B ')
insert into C(IDC,IDA,IDB,State) values(5, 2, 2, 'A ')
insert into C(IDC,IDA,IDB,State) values(6, 3, 2, 'B ')
insert into C(IDC,IDA,IDB,State) values(7, 1, 3, 'A ')
insert into C(IDC,IDA,IDB,State) values(8, 2, 3, 'C ')
insert into C(IDC,IDA,IDB,State) values(9, 3, 3, 'A ')
insert into C(IDC,IDA,IDB,State) values(10, 4, 3, 'B ')


--动态的
declare @sql varchar(1000)
set @sql= 'select b.idb,b.nameb '
select @sql=@sql+ ', '+ 'max(case when namea= ' ' ' +namea+ ' ' ' then state else ' ' ' ' end) as [ '+namea+ '] '
from (select distinct namea from a) tt
set @sql=@sql+ ' from a,b,c where a.ida=c.ida and b.idb=c.idb group by b.idb,b.nameb '
print @sql
exec(@sql)

--静态的
select b.idb,b.nameb ,
max(case when namea= 'namea1 ' then state else ' ' end) as [ namea1] ,
max(case when namea= 'namea2 ' then state else ' ' end) as [ namea2] ,
max(case when namea= 'namea3 ' then state else ' ' end) as [ namea3] ,

max(case when namea= 'namea4 ' then state else ' ' end) as [ namea4]
from a,b,c
where a.ida=c.ida and b.idb=c.idb
group by b.idb,b.nameb

drop table a,b,c

idb nameb namea1 namea2 namea3 namea4
----------- ---------- ---------- ---------- ---------- ----------
1 nameb1 A A C
2 nameb2 B A B
3 nameb3 A C A B

（所影响的行数为 3 行）