SQL server 動态行轉列

用聚合函數配合CASE語句實作行轉列功能：

現在分享一下具體實作代碼：

轉換前效果：

PlanName	PlanType	PlanLimit
計劃1	計劃類型1	RMB 1,000,000
	計劃類型2
	計劃類型3
計劃2
計劃3

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      

實作效果：

計劃

如果您看了本篇部落格,覺得對您有所收獲，請點選右下角的

[推薦]

如果您想轉載本部落格，

請注明出處

如果您對本文有意見或者建議，歡迎留言

感謝您的閱讀，請關注我的後續部落格