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.youkuaiyun.com/maco_wang 原创作品，转贴请注明作者和出处，留此信息。