报表一张
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
select '01 ', 'cpy ', 'OKI ' union all
select '03 ', 'yyc ', 'mlc '
go
--执行测试语句
select *,0 as number into #b from b order by id,lop,ccout
declare @i int,@id int,@j int
update #b
set @i = case when @id = id then @i+1 else 1 end
,@id = id,number = @i
select @i = max(number),@j = 1 from #b
declare @sql varchar(8000) set @sql = 'select a.id,a.[users],a.[procs],a.msc '
while @j <= @i
begin
select @sql = @sql + ',max(case when number = ' + convert(varchar,@j)+ ' then lop end) as lop ' + convert(varchar,@j)
+ ',max(case when number = ' + convert(varchar,@j)+ ' then ccout end) as ccout ' + convert(varchar,@j)
,@j = @j + 1
end
select @sql = @sql + ' from a join #b on #b.id = a.id group by a.id,a.[users],a.[procs],a.msc '
exec(@sql)
go
--删除测试环境
drop table a,b,#b
go
/*--测试结果
id users procs msc lop1 ccout1 lop2 ccout2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
01 A TC hjdxx coy coi cpy OKI
03 C LC OUTCCZX yyc mlc NULL NULL
*/
