SQL server 动态行转列-白红宇

用聚合函数配合CASE语句实现行转列功能：

现在分享一下具体实现代码：

转换前效果：

PlanName	PlanType	PlanLimit
计划1	计划类型1	RMB 1,000,000
计划1	计划类型2	RMB 1,000,000
计划1	计划类型3	RMB 1,000,000
计划2	计划类型1	RMB 1,000,000
计划2	计划类型2	RMB 1,000,000
计划2	计划类型3	RMB 1,000,000
计划3	计划类型1	RMB 1,000,000
计划3	计划类型2	RMB 1,000,000
计划3	计划类型3	RMB 1,000,000

1、静态实现行转列

1 with main as 2 ( 3     select '计划1' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit 4     union all 5     select '计划1' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit 6     union all 7     select '计划1' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit 8     union all 9     select '计划2' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit10     union all11     select '计划2' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit12     union all13     select '计划2' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit14     union all15     select '计划3' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit16     union all17     select '计划3' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit18     union all19     select '计划3' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit20 )21 select PlanType as [计划]22     ,'计划1'=max(case PlanName when '计划1' then PlanLimit else null end)23     ,'计划2'=max(case PlanName when '计划2' then PlanLimit else null end)24     ,'计划3'=max(case PlanName when '计划3' then PlanLimit else null end)25 from main 26 where 1=127 group by PlanType

2、动态实现行转列

1 -- ============================================= 2 -- Author:        
     
       3 -- Create date: <2016/04/27> 4 -- Description:    
      <查询sp_annetest表的数据>
        5 -- ============================================= 6 CREATE PROCEDURE [dbo].[SP_AnneTest] 7 ( 8 ) 9 AS10     declare @sql varchar(8000)11 BEGIN12     set @sql=''13 14     select @sql=@sql+','+''''+[PlanName]+''''+'=max(case PlanName when '''+[PlanName]+''' then PlanLimit else null end)'15     from main16     where 1=117     group by PlanType18     set @sql='select PlanType as [''计划'']'+@sql+' 19             from main 20             where 1=121             group by PlanType'22     23     print @sql24     exec(@sql)25 26 END27 GO

实现效果：

计划	计划1	计划2	计划3
计划类型1	RMB 1,000,000	RMB 1,000,000	RMB 1,000,000
计划类型2	RMB 1,000,000	RMB 1,000,000	RMB 1,000,000
计划类型3	RMB 1,000,000	RMB 1,000,000	RMB 1,000,000

如果您看了本篇博客,觉得对您有所收获，请点击右下角的 [推荐]

如果您想转载本博客，请注明出处

如果您对本文有意见或者建议，欢迎留言

感谢您的阅读，请关注我的后续博客