SQL server 动态行转列

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

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

转换前效果:

PlanNamePlanTypePlanLimit
计划1计划类型1RMB 1,000,000
计划1计划类型2RMB 1,000,000
计划1计划类型3RMB 1,000,000
计划2计划类型1RMB 1,000,000
计划2计划类型2RMB 1,000,000
计划2计划类型3RMB 1,000,000
计划3计划类型1RMB 1,000,000
计划3计划类型2RMB 1,000,000
计划3计划类型3RMB 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 PlanLimit
10     union all
11     select '计划2' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit
12     union all
13     select '计划2' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit
14     union all
15     select '计划3' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit
16     union all
17     select '计划3' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit
18     union all
19     select '计划3' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit
20 )
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=1
27 group by PlanType

2、动态实现行转列

 1 -- =============================================
 2 -- Author:        <Anne>
 3 -- Create date: <2016/04/27>
 4 -- Description:    <查询SP_AnneTest表的数据>
 5 -- =============================================
 6 CREATE PROCEDURE [dbo].[SP_AnneTest]
 7 (
 8 )
 9 AS
10     declare @sql varchar(8000)
11 BEGIN
12     set @sql=''
13 
14     select @sql=@sql+','+''''+[PlanName]+''''+'=max(case PlanName when '''+[PlanName]+''' then PlanLimit else null end)'
15     from main
16     where 1=1
17     group by PlanType
18     set @sql='select PlanType as [''计划'']'+@sql+' 
19             from main 
20             where 1=1
21             group by PlanType'
22     
23     print @sql
24     exec(@sql)
25 
26 END
27 GO

 

实现效果:

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

 

转载于:https://www.cnblogs.com/AnneHan/p/5541961.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值