资源描述：
《sqlserver纯粹意义上的行列转换》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、首先还是用例子来说明一下这个我所谓的“纯粹意义” 假设有下面这样一个表： CID AID Q-------------------C1 M1 1C2 M1 2C3 M1 3C4 M2 1C5 M2 2我们要得到的效果是这样的： 字段 1 2 3 4 5------------------------AID M1 M1 M1 M2 M2Q 1 2 3 1 2CID C1 C2 C3 C
2、4 C5 就是列变行，不需要做其他处理。 下面示例可以实现这个效果： declare @C table (CID varchar(2),AID varchar(2),Q int)insert into @Cselect 'C1','M1',1 union allselect 'C2','M1',2 union allselect 'C3','M1',3 union allselect 'C4','M2',1 union allselect 'C5','M2',2 select * from @C ;with
3、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
4、'' 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 #tfrom Cgroup by (row-1)/6 ;with D as(select row_number()over(order by CID) row,AID from @C ) insert into #tselect 'AI
5、D' 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), [
6、5]=Max(case when row%6=5 then RTRIM(AID) else '' end)from Dgroup by (row-1)/6 ;with E as(select row_number()over(order by CID) row,Q from @C ) insert into #tselect 'Q' as 字段, [1]=Max(case when row%6=1 then RTRIM(Q) else '' end), [2]=Max(case
7、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 Egroup by (row-1)/6 select * from
8、 #t drop table #t /*CID AID Q-------------------C1 M1 1C2 M1 2C3 M1 3C4 M2 1C5 M2 2 字段 1 2 3 4 5------------------------AID M1 M1 M1 M2 M2Q
