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

'C2',2 union all

'C3',3 union all

'C4','M2',"serif"; mso-no-proof: yes;'> 'C5',2

* from @C

?

;with C as

(

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

)

??? '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),"serif"; mso-no-proof: yes;'>??? [3]=Max(case when row%6=3 then? RTRIM(CID) else ''? end),"serif"; mso-no-proof: yes;'>??? [4]=Max(case when row%6=4 then? RTRIM(CID) else '' end),"serif"; mso-no-proof: yes;'>??? [5]=Max(case when row%6=5 then? RTRIM(CID) else ''? end) into #t

from??? C

group by (row-1)/6

D as

into #t

??? 'AID' as 字段??? [1]=Max(case when row%6=1 then??? RTRIM(AID) else '' end),"serif"; mso-no-proof: yes;'>??? [2]=Max(case when row%6=2 then??? RTRIM(AID) else '' end),"serif"; mso-no-proof: yes;'>??? [3]=Max(case when row%6=3 then? RTRIM(AID) else ''? end),"serif"; mso-no-proof: yes;'>??? [4]=Max(case when row%6=4 then? RTRIM(AID) else '' end),"serif"; mso-no-proof: yes;'>??? [5]=Max(case when row%6=5 then? RTRIM(AID) else ''? end)

??? D

E as

??? 'Q' as 字段??? [1]=Max(case when row%6=1 then??? RTRIM(Q) else '' end),"serif"; mso-no-proof: yes;'>??? [2]=Max(case when row%6=2 then??? RTRIM(Q) else '' end),"serif"; mso-no-proof: yes;'>??? [3]=Max(case when row%6=3 then? RTRIM(Q) else ''? end),"serif"; mso-no-proof: yes;'>??? [4]=Max(case when row%6=4 then? RTRIM(Q) else '' end),"serif"; mso-no-proof: yes;'>??? [5]=Max(case when row%6=5 then? RTRIM(Q) else ''? end)

??? E

* 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

*/

?

?

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

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

into @表结构

'CID','varchar' union all

'AID',"serif"; mso-no-proof: yes;'> 'Q','int' union all

'CName','varchar'

* from @表结构

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

/*

字段?? 类型

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

CID?? varchar

AID?? varchar

Q???? int

CName varchar

?? 类型???? 字段----- ------- ---- ---- ---- ---- ---- ----

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!