SQL SERVER數據庫縱表轉橫表應用淺析

摘要:在管理系統開發中，經常遇到縱表轉橫表或者橫表轉縱表的需求，由于發現各語法都能達到需求，或者有些語法在字段不是很多的時候能達到，但是運行效率相差很多，下面將遇到的各種語法進行總結，希望對同行在有需要的情況下能有所幫助。

關鍵詞:數據庫 sql server 縱表轉橫表

0 引言

模型描述:

將下圖所示的縱表(表1)的數據轉為橫表(表2)，在同一報表中同時顯示每個人員的不同月份的銷售金額，年度暫定為2009年。

具體實現:

在系統中，有三個表，字段信息如下

表1:人員、回款、回款日期;

經過分析sql server的應用總結以及對sqlserver幫助潛心專研，感觸頗深，現總結如下與各位朋友共同分享，如有更好或者更方便的建議，請賜教，在此先謝過。本方法適用SQLserver2000及以上。

為了直觀些，表名與字段名稱等信息一律使用漢字名稱。

建表語句如下所示:

CREATE TABLE [dbo].[表1] (

[ID] [int] IDENTITY (1， 1) NOT NULL ，

[人員] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL

[合同金額] [decimal](18， 0) NULL ，

[合同日期] [datetime] NULL

) ON [PRIMARY]

建立一個視圖，如下語句所示:

CREATE VIEW dbo.VIEW1 AS

SELECT 人員， 銷售金額， YEAR(銷售日期) AS 年度， MONTH(銷售日期) AS 月份

FROM dbo.表1

利用下面的語句插入數據

insert into dbo.表1 (人員，銷售金額，銷售日期)

select '張三' ，12，'2009-01-01' union all

select '張三' ，86，'2009-07-09' union all

select '張三' ，89，'2009-08-21' union all

select '張三' ，88，'2009-12-31' union all

select '李四' ，99，'2009-02-21' union all

select '李四' ，36，'2009-03-01' union all

select '李四' ，68，'2009-06-11' union all

select '李四' ，88，'2009-09-21' union all

select '王五' ，89，'2009-03-31' union all

select '王五' ，99，'2009-10-31' union all

select '趙六' ，78，'2009-05-31' union all

select '趙六' ，65，'2009-11-21'

通過下面的視圖將各月份的銷售金額進行匯總:

CREATE VIEW dbo.VIEW2

AS SELECT 人員， 月份， SUM(銷售金額) AS銷售金額

FROM dbo.VIEW1 GROUP BY 人員， 年度， 月份

經過以上準備，各項基本信息齊備，下面將通過VIEW2視圖得到的數據分別說明縱表轉橫表sql語句

1 通過求最大值的方法，此法效率相對較低

SELECT 人員， MAX(a) AS '1'，MAX(b) AS '2'， MAX(c) AS '3'， MAX(d) AS '4'， MAX(e) AS '5'， MAX(f) AS '6'， MAX(g) AS '7'， MAX(h) AS '8'， MAX(i) AS '9'， MAX(j) AS '10'， MAX(k) AS '11'， MAX(l) AS '12' FROM(

SELECT 人員， CASE 月份 WHEN 1 THEN 銷售金額 END AS 'a'

，CASE 月份 WHEN 2 THEN 銷售金額 END AS 'b'

，CASE 月份 WHEN 3 THEN 銷售金額 END AS 'c'

，CASE 月份 WHEN 4 THEN 銷售金額 END AS 'd'

，CASE 月份 WHEN 5 THEN 銷售金額 END AS 'e'

，CASE 月份 WHEN 6 THEN 銷售金額 END AS 'f'

，CASE 月份 WHEN 7 THEN 銷售金額 END AS 'g'

，CASE 月份 WHEN 8 THEN 銷售金額 END AS 'h'

，CASE 月份 WHEN 9 THEN 銷售金額 END AS 'i'

，CASE 月份 WHEN 10 THEN 銷售金額 END AS 'j'

，CASE 月份 WHEN 11 THEN 銷售金額 END AS 'k'

，CASE 月份 WHEN 12 THEN 銷售金額 END AS 'l'

FROM view2) AS a GROUP BY 人員 order by 人員

結果如下:

2 表鏈接法，此法效率極低，但是也可以得到同樣結果

select distinct(view2.人員)，tt1.銷售金額 '1' ，tt2.銷售金額 '2' ，tt3.銷售金額 '3'，tt4.銷售金額 '4'，tt5.銷售金額 '5'，tt6.銷售金額 '6'，tt7.銷售金額 '7'，tt8.銷售金額 '8'，tt9.銷售金額 '9'，tt10.銷售金額 '10'，tt11.銷售金額 '11'，

tt12.銷售金額 '12' from view2

left join (select 人員，銷售金額 from view2 where 月份 = 1) tt1 on tt1.人員=view2.人員

left join (select 人員，銷售金額 from view2 where 月份 = 2) tt2 on tt2.人員=view2.人員

……

left join (select 人員，銷售金額 from view2 where 月份 = 12) tt12 on tt12.人員=view2.人員

結果如1所示。

3 嵌套選擇方法，效率也是較低

select 人員，

\"1\"=(select 銷售金額 from view2 where 月份=1 and 人員=t.人員)，

\"2\"=(select 銷售金額 from view2 where 月份=2 and 人員=t.人員)，

\"3\"=(select 銷售金額 from view2 where 月份=3 and 人員=t.人員)，

……

\"12\"=(select 銷售金額 from view2 where 月份=12 and 人員=t.人員)

from view2 t group by 人員

結果如1所示。

4 分組求和方法，此方法較為合適

select 人員， sum(case when 月份 = 1 then 銷售金額end) '1'，

sum(case when 月份 = 2 then 銷售金額end) '2'，

……

sum(case when 月份 = 12 then 銷售金額end) '12'

from view2 t group by 人員

結果如1所示。

5 分組求和方法，此方法也較為合適

select 人員，max(case when 月份 = 1 then 銷售金額end) '1'

， max(case when 月份 = 2 then 銷售金額end) '2'

……

， max(case when 月份 = 12 then 銷售金額end) '12'

from view2 t group by 人員

結果如1所示。

應用分析:

本文對縱表轉橫表多數sql語句進行了分析，分別列出了相應的查詢效率。在管理系統中應用較多，如何提高查詢效率，將直接影響到管理系統分析的效果。高效率的查詢，將會極高的提高管理系統的實用性。

參考文獻:

[1]sql server 聯機叢書.

[2]SQL Server 2000從入門到精通.作者Joseph L.Jorden Mike Gunderloy Joseph L.Jorden著.電子工業出版社/2001-03-01.