报表一张
a表

id   user   proc     msc
01     A         TC         hjdxx
02     B         MC         JDCXX
03     C         LC           OUTCCZX

b表

id   LOP   ccout
01   coy     coi
01   cpy     OKI
03   yyc     mlc  


有两个主子表，想查询成如下


id     user     proc         msc               LOP         ccout     (   LOP       ccout)....动态增加   主要看子表中的数据

01     A             TC           hjdxx           coy           coi         cpy         OKI

03     C             LC           OUTCCZX         yyc         mlc  


------解决方案--------------------
try

--如果b表沒有主鍵，需要借助臨時表
Select ID2 = Identity(Int, 1, 1), * Into #T From b

Declare @S Varchar(8000)
Select @S = ' Select T1.* '
Select @S = @S + ', Max(Case T1.OrderID When ' + Cast(OrderID As Varchar) + ' Then LOP Else Null End) As LOP ' + Cast(OrderID As Varchar)
+ ', Max(Case T1.OrderID When ' + Cast(OrderID As Varchar) + ' Then ccout Else Null End) As ccout ' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID2) From #T Where Id = A.id And ID2 <= A.ID2), * From #T A) T1
Select @S = @S + ' From (Select OrderID = (Select Count(ID2) From #T Where Id = A.id And ID2 <= A.ID2), * From #T A) T1 Inner Join b T2 On T1.id = T2.id Group By T1.id, T1.[user], T1.proc, T1.msc '
EXEC(@S)

Drop Table #T
------解决方案--------------------
思路沒錯，但是語法有很多問題，修改下

--如果b表沒有主鍵，需要借助臨時表
Select ID2 = Identity(Int, 1, 1), * Into #T From b

Declare @S Varchar(8000)
Select @S = ' Select T1.* '
Select @S = @S + ', Max(Case T2.OrderID When ' + Cast(OrderID As Varchar) + ' Then T2.LOP Else Null End) As LOP ' + Cast(OrderID As Varchar)
+ ', Max(Case T2.OrderID When ' + Cast(OrderID As Varchar) + ' Then T2.ccout Else Null End) As ccout ' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(ID2) From #T Where Id = A.id And ID2 <= A.ID2), * From #T A) T1 Group By OrderID
Select @S = @S + ' From a T1 Inner Join (Select OrderID = (Select Count(ID2) From #T Where Id = A.id And ID2 <= A.ID2), * From #T A) T2 On T1.id = T2.id Group By T1.id, T1.[user], T1.[proc], T1.msc '
Print @S
EXEC(@S)

Drop Table #T
------解决方案--------------------
--建立测试环境
create table a(id varchar(10),[users] varchar(10),[procs] varchar(10),msc varchar(10))
insert a(id,[users],[procs],msc)
select '01 ', 'A ', 'TC ', 'hjdxx ' union all
select '02 ', 'B ', 'MC ', 'JDCXX ' union all
select '03 ', 'C ', 'LC ', 'OUTCCZX '
go
create table b(id varchar(10),LOP varchar(10),ccout varchar(10))
insert b(id,LOP,ccout)
select '01 ', 'coy ', 'coi ' union all
selec