交叉表问题，请赐教!

[doumingxuan]

create table tabpo(weekno nvarchar(10) not null,ponumber nvarchar(15) not null)
insert tabpo select 'we0403','PO001'
union all select 'we0403','PO002'
union all select 'we0403','PO003'
union all select 'we0403','PO004'
union all select 'we0403','PO005'
union all select 'we0410','PO006'
union all select 'we0410','PO007'
union all select 'we0410','PO008'
union all select 'we0417','PO009'
union all select 'we0417','PO010'
union all select 'we0424','PO011'
union all select 'we0424','PO012'
union all select 'we0424','PO013'
union all select 'we0424','PO014'

结果如下：

we0403 we0410 we0417 we0424 ...
PO001 PO006 PO009 PO011
PO002 PO007 PO010 PO012
PO003 PO008 PO013
PO004 PO014
PO005

[paoluo]

--建立測試環境
create table tabpo(weekno nvarchar(10) not null,ponumber nvarchar(15) not null)
--插入數據
insert tabpo select 'we0403','PO001'
union all select 'we0403','PO002'
union all select 'we0403','PO003'
union all select 'we0403','PO004'
union all select 'we0403','PO005'
union all select 'we0410','PO006'
union all select 'we0410','PO007'
union all select 'we0410','PO008'
union all select 'we0417','PO009'
union all select 'we0417','PO010'
union all select 'we0424','PO011'
union all select 'we0424','PO012'
union all select 'we0424','PO013'
union all select 'we0424','PO014'
Go
--測試
Declare @I Int
Declare @ID Int
Declare @S Varchar(8000)
Set @S=''

Select ID=(Select Count(*) from tabpo Where weekno=A.weekno And ponumber<=A.ponumber ),* Into TEST from tabpo A
Set @I=1
Select @ID=Max(ID) from TEST
While @I<=@ID
Begin
Set @S=@S+'Select '
Select @S=@S+'Max((Case When weekno='''+weekno+''' Then ponumber Else '''' End)) As '''+weekno+''',' from (Select Distinct weekno from TEST) A
Select @S=Left(@S,Len(@S)-1)+' from TEST Where ID='+Rtrim(@I)+' Union All '
Set @I=@I+1
End
Select @S=Left(@S,Len(@S)-10)
--Select @S
EXEC(@S)
--刪除測試環境
Drop Table tabpo,TEST
--結果
/*
we0403 we0410 we0417 we0424
PO001 PO006 PO009 PO011
PO002 PO007 PO010 PO012
PO003 PO008 PO013
PO004 PO014
PO005
*/

[xeqtr1982]

向大侠学习　：）

[eddy8863]

create table tabpo(weekno varchar(10) not null,ponumber varchar(15) not null)
insert tabpo select 'we0403','PO001'
union all select 'we0403','PO002'
union all select 'we0403','PO003'
union all select 'we0403','PO004'
union all select 'we0403','PO005'
union all select 'we0410','PO006'
union all select 'we0410','PO007'
union all select 'we0410','PO008'
union all select 'we0417','PO009'
union all select 'we0417','PO010'
union all select 'we0424','PO011'
union all select 'we0424','PO012'
union all select 'we0424','PO013'
union all select 'we0424','PO014'
go

select [id]=(select count(1) from tabpo where weekno=a.weekno and ponumber<=a.ponumber ),* into # from tabpo a
declare @str varchar(8000)
select @str=''
select @str=@str+',['+weekno+']=max(case weekno when '''+weekno+''' then ponumber else '''' end)' from # group by weekno
select @str=stuff(@str,1,1,'')
select @str='select '+@str+' from # group by id order by id'
--print @str
exec(@str)

drop table Tabpo
drop table #

we0403 we0410 we0417 we0424
--------------- --------------- --------------- ---------------
PO001 PO006 PO009 PO011
PO002 PO007 PO010 PO012
PO003 PO008 PO013
PO004 PO014
PO005