Sqlserver 纯粹意义上的行列转换--【叶子】

首先还是用例子来说明一下这个我所谓的“纯粹意义”

 

假设有下面这样一个表：

 

CID  AID  Q

---- ---- -----------

C1   M1   1

C2   M1   2

C3   M1   3

C4   M2   1

C5   M2   2

我们要得到的效果是这样的：

 

字段  1    2    3    4    5

---- ---- ---- ---- ---- ----

AID  M1   M1   M1   M2   M2

Q    1    2    3    1    2

CID  C1   C2   C3   C4   C5

 

就是列变行，不需要做其他处理。

 

下面示例可以实现这个效果：

 

declare @C table (CID varchar(2),AID varchar(2),Q int)

insert into @C

select 'C1','M1',1 union all

select 'C2','M1',2 union all

select 'C3','M1',3 union all

select 'C4','M2',1 union all

select 'C5','M2',2

 

select * from @C

 

;with C as

(

select row_number()over(order by CID) row,CID from @C 

)

 

select

    'CID' as 字段,

    [1]=Max(case when row%6=1 then    RTRIM(CID) else '' end),

    [2]=Max(case when row%6=2 then    RTRIM(CID) else '' end),

    [3]=Max(case when row%6=3 then  RTRIM(CID) else ''  end),

    [4]=Max(case when row%6=4 then  RTRIM(CID) else '' end),

    [5]=Max(case when row%6=5 then  RTRIM(CID) else ''  end) into #t

from

    C

group by (row-1)/6

 

;with D as

(

select row_number()over(order by CID) row,AID from @C 

)

 

insert into #t

select

    'AID' as 字段,

    [1]=Max(case when row%6=1 then    RTRIM(AID) else '' end),

    [2]=Max(case when row%6=2 then    RTRIM(AID) else '' end),

    [3]=Max(case when row%6=3 then  RTRIM(AID) else ''  end),

    [4]=Max(case when row%6=4 then  RTRIM(AID) else '' end),

    [5]=Max(case when row%6=5 then  RTRIM(AID) else ''  end)

from

    D

group by (row-1)/6

 

;with E as

(

select row_number()over(order by CID) row,Q from @C 

)

 

insert into #t

select

    'Q' as 字段,

    [1]=Max(case when row%6=1 then    RTRIM(Q) else '' end),

    [2]=Max(case when row%6=2 then    RTRIM(Q) else '' end),

    [3]=Max(case when row%6=3 then  RTRIM(Q) else ''  end),

    [4]=Max(case when row%6=4 then  RTRIM(Q) else '' end),

    [5]=Max(case when row%6=5 then  RTRIM(Q) else ''  end)

from

    E

group by (row-1)/6

 

select * from #t

 

 

drop table #t

 

/*

CID  AID  Q

---- ---- -----------

C1   M1   1

C2   M1   2

C3   M1   3

C4   M2   1

C5   M2   2

 

 

字段  1    2    3    4    5

---- ---- ---- ---- ---- ----

AID  M1   M1   M1   M2   M2

Q    1    2    3    1    2

CID  C1   C2   C3   C4   C5

*/

 

 

我们也可以在创建一个表来放表结构。

 

declare @表结构table (字段varchar(5),类型varchar(7))

insert into @表结构

select 'CID','varchar' union all

select 'AID','varchar' union all

select 'Q','int' union all

select 'CName','varchar'

 

select * from @表结构

 

 

declare @C table (CID varchar(2),AID varchar(2),Q int)

insert into @C

select 'C1','M1',1 union all

select 'C2','M1',2 union all

select 'C3','M1',3 union all

select 'C4','M2',1 union all

select 'C5','M2',2

 

select * from @C

 

;with C as

(

select row_number()over(order by CID) row,CID from @C 

)

 

select

    'CID' as 字段,

    [1]=Max(case when row%6=1 then    RTRIM(CID) else '' end),

    [2]=Max(case when row%6=2 then    RTRIM(CID) else '' end),

    [3]=Max(case when row%6=3 then  RTRIM(CID) else ''  end),

    [4]=Max(case when row%6=4 then  RTRIM(CID) else '' end),

    [5]=Max(case when row%6=5 then  RTRIM(CID) else ''  end) into #t

from

    C

group by (row-1)/6

 

;with D as

(

select row_number()over(order by CID) row,AID from @C 

)

 

insert into #t

select

    'AID' as 字段,

    [1]=Max(case when row%6=1 then    RTRIM(AID) else '' end),

    [2]=Max(case when row%6=2 then    RTRIM(AID) else '' end),

    [3]=Max(case when row%6=3 then  RTRIM(AID) else ''  end),

    [4]=Max(case when row%6=4 then  RTRIM(AID) else '' end),

    [5]=Max(case when row%6=5 then  RTRIM(AID) else ''  end)

from

    D

group by (row-1)/6

 

;with E as

(

select row_number()over(order by CID) row,Q from @C 

)

 

insert into #t

select

    'Q' as 字段,

    [1]=Max(case when row%6=1 then    RTRIM(Q) else '' end),

    [2]=Max(case when row%6=2 then    RTRIM(Q) else '' end),

    [3]=Max(case when row%6=3 then  RTRIM(Q) else ''  end),

    [4]=Max(case when row%6=4 then  RTRIM(Q) else '' end),

    [5]=Max(case when row%6=5 then  RTRIM(Q) else ''  end)

from

    E

group by (row-1)/6

 

select * from @表结构a left join  #t b on a.字段=b.字段

 

drop table #t

 

/*

字段   类型

----- -------

CID   varchar

AID   varchar

Q     int

CName varchar

 

CID  AID  Q

---- ---- -----------

C1   M1   1

C2   M1   2

C3   M1   3

C4   M2   1

C5   M2   2

 

字段   类型     字段  1    2    3    4    5

----- ------- ---- ---- ---- ---- ---- ----

CID   varchar CID  C1   C2   C3   C4   C5

AID   varchar AID  M1   M1   M1   M2   M2

Q     int     Q    1    2    3    1    2

CName varchar NULL NULL NULL NULL NULL NULL

*/

 

@【叶子】http://blog.csdn.net/maco_wang 原创作品，转贴请注明作者和出处，留此信息。