sql server 2005 行列转置

  
  

   
   

    
    原始数据：
   
   
   
   

    
     
   
   
   
   

    
    转置后数据：
   
   
  
  
  
  

   
    
  
  

-- populate some test data
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (
    ID int identity(1,1) not null,
    MainField varchar(100),
    ThatField int,
    ThatOtherField datetime
)

insert into #tmp (MainField, ThatField, ThatOtherField)
select 'A', 10, '1/1/2000' union all
select 'A', 20, '2/1/2000' union all
select 'A', 30, '3/1/2000' union all
select 'B', 10, '1/1/2001' union all
select 'B', 20, '2/1/2001' union all
select 'B', 30, '3/1/2001' union all
select 'B', 40, '4/1/2001' union all
select 'C', 10, '1/1/2002' union all
select 'D', 10, '1/1/2000' union all
select 'D', 20, '2/1/2000' --union all

-- pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence trick
select
    MainField,
    max([1.1]) as ThatField1,
    max([1.2]) as ThatOtherField1,
    max([2.1]) as ThatField2,
    max([2.2]) as ThatOtherField2,
    max([3.1]) as ThatField3,
    max([3.2]) as ThatOtherField3,
    max([4.1]) as ThatField4,
    max([4.2]) as ThatOtherField4
from

(
    (
        select x.*,
            cast(row_number() over (partition by MainField order by ThatField) as varchar(2)) + '.1' as ThatFieldSequence,
            cast(row_number() over (partition by MainField order by ThatField) as varchar(2)) + '.2' as ThatOtherFieldSequence
        from #tmp x
    ) a
    pivot (
        max(ThatField) for ThatFieldSequence in ([1.1], [2.1], [3.1], [4.1])
    ) p1
    pivot (
        max(ThatOtherField) for ThatOtherFieldSequence in ([1.2], [2.2], [3.2], [4.2])
    ) p2

)ddd
group by
    MainField