行列转换之——多行转多列，多列转多行实践版

参考：深入行列转换—-多行转多列，多行的计算

参考：sql server动态行列转换

 

1、多列转行（核心思想，利用row_number() over() 来构造列传行之后的唯一列，来行转列）

 　　要求：

　　　

 实操演示：

select \'a\' as \'a\',\'b\' as \'b\',\'c\' as \'c\'
into #temp1
union all
select \'aa\',\'bb\',\'cc\'
union all 
select \'aaa\',\'bbb\',\'ccc\'

select * from 
(
    select column1,value,row_number() over(partition by column1 order by value) as rn from #temp1
    unpivot(value for column1 in(a,b,c) ) t
) a
pivot(max(value) for rn in ([1],[2],[3])) t1

 

 

 

2、行转多列（核心row_number()）

 

select \'a\' as \'a\',\'b\' as \'b\',\'c\' as \'c\'
into #temp2
union all
select \'a\' as \'a\',\'b1\' as \'b\',\'c1\' as \'c\'
union all
select \'aa\',\'bb\',\'cc\'
union all 
select \'aa\',\'bb1\',\'cc1\'
union all
select \'aaa\',\'bbb\',\'ccc\'
select * from #temp2
--1.全表
select * from #temp2
--2.构造row_number()
    select *,row_number() over(partition by a order by a) as \'rn\' from  #temp2
--3.行转列
select a, max(case when rn = 1 then A else null end) [B_1],max(case when rn = 1 then B else null end) [C_1],
max(case when rn = 2 then A else null end) [B_2],max(case when rn = 2 then B else null end) [C_2]
from 
(
    select *,row_number() over(partition by a order by a) as \'rn\' from  #temp2
) a
GROUP BY A


 



 

3.直接行转列

　　

select \'a\' as [c1],1 as \'c2\'
union all
select \'b\' as [c1],2 as \'c2\'

;with temp1 as (
select \'a\' as [c1],1 as \'c2\'
union all
select \'b\' as [c1],2 as \'c2\'
)
select 
max(case when rn=1 then c1 else null end) as [c1_1],
max(case when rn=1 then c2 else null end) as [c2_1],
max(case when rn=2 then c1 else null end) as [c1_1],
max(case when rn=2 then c2 else null end) as [c2_1]
from 
(select *,row_number() over(order by c1) as [rn] from temp1) t

 





 






        





							

   
版权声明：本文为gered原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。



本文链接：https://www.cnblogs.com/gered/p/10289013.html