try--如果b表沒有主鍵,需要借助臨時表
Select ID2 = Identity(Int, 1, 1), * Into #T From bDeclare @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
Select ID2 = Identity(Int, 1, 1), * Into #T From bDeclare @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
Select ID2 = Identity(Int, 1, 1), * Into #T From bDeclare @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 Char(2),
[user] Varchar(10),
[proc] Varchar(10),
msc Varchar(10))
Insert a Select '01', 'A', 'TC', 'hjdxx'
Union All Select '02', 'B', 'MC', 'JDCXX'
Union All Select '03', 'C', 'LC', 'OUTCCZX'Create Table b
(id Char(2),
LOP Varchar(10),
ccout Varchar(10))
Insert b Select '01', 'coy', 'coi'
Union All Select '01', 'cpy', 'OKI'
Union All Select '03', 'yyc', 'mlc'
GO
--測試
--如果b表沒有主鍵,需要借助臨時表
Select ID2 = Identity(Int, 1, 1), * Into #T From bDeclare @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
GO
--刪除測試環境
Drop Table a, b
--結果
/*
id user proc msc LOP1 ccout1 LOP2 ccout2
01 A TC hjdxx coy coi cpy OKI
03 C LC OUTCCZX yyc mlc NULL NULL
*/
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 = @iselect @i = max(number),@j = 1 from #bdeclare @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
*/