行列转换之——多行转多列,多列转多行实践版
行列转换之——多行转多列,多列转多行实践版
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