當前位置:首頁 > IT技術 > 數(shù)據(jù)庫 > 正文

sqlserver 行列轉(zhuǎn)換
2021-09-03 20:18:19

我們在寫Sql語句的時候沒經(jīng)常會遇到將查詢結果行轉(zhuǎn)列,列轉(zhuǎn)行的需求,拼接sql字符串,然后使用sp_executesql執(zhí)行sql字符串是比較常規(guī)的一種做法。但是這樣做實現(xiàn)起來非常復雜,而在SqlServer2005中我們有了PIVOT/UNPIVOT函數(shù)可以快速實現(xiàn)行轉(zhuǎn)列和列轉(zhuǎn)行的操作。

?

PIVOT函數(shù),行轉(zhuǎn)列

?

PIVOT函數(shù)的格式如下

PIVOT(<聚合函數(shù)>([聚合列值]) FOR [行轉(zhuǎn)列前的列名] IN([行轉(zhuǎn)列后的列名1],[行轉(zhuǎn)列后的列名2],[行轉(zhuǎn)列后的列名3],.......[行轉(zhuǎn)列后的列名N]))
  • <聚合函數(shù)>就是我們使用的SUM,COUNT,AVG等Sql聚合函數(shù),也就是行轉(zhuǎn)列后計算列的聚合方式。
  • [聚合列值]要進行聚合的列名
  • [行轉(zhuǎn)列前的列名]這個就是需要將行轉(zhuǎn)換為列的列名。
  • [行轉(zhuǎn)列后的列名]這里需要聲明將行的值轉(zhuǎn)換為列后的列名,因為轉(zhuǎn)換后的列名其實就是轉(zhuǎn)換前行的值,所以上面格式中的[行轉(zhuǎn)列后的列名1],[行轉(zhuǎn)列后的列名2],[行轉(zhuǎn)列后的列名3],......[行轉(zhuǎn)列后的列名N]其實就是[行轉(zhuǎn)列前的列名]每一行的值。

?

下面我們來看一個例子有一張表名為[ShoppingCart]有三列[Week],[TotalPrice],[GroupId],數(shù)據(jù)和表結構如下所示:

復制代碼
復制代碼
CREATE TABLE [dbo].[ShoppingCart](
    [Week] [int] NOT NULL,
    [TotalPrice] [decimal](18, 0) NOT NULL,
    [GroupId] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ShoppingCart] ADD  DEFAULT ((0)) FOR [TotalPrice]
GO

INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (1, CAST(10 AS Decimal(18, 0)), 1)
GO
INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (2, CAST(20 AS Decimal(18, 0)), 1)
GO
INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (3, CAST(30 AS Decimal(18, 0)), 1)
GO
INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (4, CAST(40 AS Decimal(18, 0)), 1)
GO
INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (5, CAST(50 AS Decimal(18, 0)), 1)
GO
INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (6, CAST(60 AS Decimal(18, 0)), 1)
GO
INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (7, CAST(70 AS Decimal(18, 0)), 1)
GO
復制代碼
復制代碼

現(xiàn)在我們是用PIVOT函數(shù)將列[WEEK]的行值轉(zhuǎn)換為列,并使用聚合函數(shù)Count(TotalPrice)來統(tǒng)計每一個Week列在轉(zhuǎn)換前有多少行數(shù)據(jù),語句如下所示:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 

查詢結果如下:

我們可以看到PIVOT函數(shù)成功地將[ShoppingCart]表列[Week]的行值轉(zhuǎn)換為了七列,并且每一列統(tǒng)計轉(zhuǎn)換前的行數(shù)為1,這符合我們的預期結果。那么根據(jù)我們前面定義的PIVOT函數(shù)轉(zhuǎn)換格式,在本例中我們有如下公式對應值:

  • <聚合函數(shù)>本例中為Count
  • [聚合列值]本例中為[TotalPrice],統(tǒng)計了行轉(zhuǎn)列前的行數(shù)
  • [行轉(zhuǎn)列前的列名]本例中為[Week]
  • [行轉(zhuǎn)列后的列名]本例中為[1],[2],[3],[4],[5],[6],[7]七個列,因為行轉(zhuǎn)列前[ShoppingCart]表的[Week]列有七個值1,2,3,4,5,6,7,所以這里聲明轉(zhuǎn)換后的列名也為七個,對應這七個值分別為[1],[2],[3],[4],[5],[6],[7],PIVOT函數(shù)會將[ShoppingCart]表中[Week]列的值分別和[1],[2],[3],[4],[5],[6],[7]這七列的列名進行匹配,然后計算<聚合函數(shù)>(本例中為count(TotalPrice))得出轉(zhuǎn)換后的列值。

另外如果我們在[行轉(zhuǎn)列后的列名]中只聲明了部分值,那么PIVOT函數(shù)只會針對這些部分值做行轉(zhuǎn)列,而那些沒有被聲明為列的行值會在行轉(zhuǎn)列后被忽略掉。例如我們下面的語句聲明了只對表ShoppingCart中[Week]列的1,2,3三個值做行轉(zhuǎn)列,但是實際上表ShoppingCart中列[Week]有1,2,3,4,5,6,7這7個值,那么剩下的4到7就會被PIVOT函數(shù)忽略掉,如下所示:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3])) AS T 

我們可以看到查詢結果中PIVOT函數(shù)只針對表ShoppingCart中列[Week]的1,2,3三個值做了行轉(zhuǎn)列,而4到7被忽略了。

?

需要注意的是PIVOT函數(shù)的查詢結果中多了一列GroupId,這是因為PIVOT函數(shù)只用到了[ShoppingCart]表中的列[Week]和[TotalPrice],[ShoppingCart]表中還有一列[GroupId],PIVOT函數(shù)沒有用到,所以PIVOT函數(shù)默認將[ShoppingCart]表中沒有用到的列當做了Group By來處理,用來作為行轉(zhuǎn)列后每一行數(shù)據(jù)分行的依據(jù),又由于列[GroupId]在[ShoppingCart]表中全為值1,所以最后PIVOT函數(shù)在行轉(zhuǎn)列后只有一行[GroupId]為1的數(shù)據(jù),如果我們將[ShoppingCart]表列[GroupId]的值從只有1變成有1和2兩種值,如下所示:

然后再執(zhí)行PIVOT查詢:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 

會得到如下結果:

我們看到這一次我們用PIVOT函數(shù)做行轉(zhuǎn)列后得到了兩行值,可以看到轉(zhuǎn)換后列[3]和[4]在[GroupId]為2的這一行上為1,這就是因為我們將[ShoppingCart]表中[Week]為3和4的兩行改成了[GroupId]為2后,[GroupId]有了兩個值1和2,所以PIVOT函數(shù)行轉(zhuǎn)列后就有兩行值。

?

知道了PIVOT函數(shù)的用法之后,我們來看看PIVOT函數(shù)的幾種錯誤用法:

在PIVOT函數(shù)的使用中有一點需要注意,那就是[行轉(zhuǎn)列后的列名]必須是[行轉(zhuǎn)列前的列名]的值,PIVOT函數(shù)才能成功執(zhí)行,比如如下所示如果我們將[行轉(zhuǎn)列后的列名]聲明了一個和[行轉(zhuǎn)列前的列名]值毫不相干的數(shù)字1000,那么PIVOT函數(shù)執(zhí)行后1000是沒有任何數(shù)據(jù)的為0:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7],[1000])) AS T 

這是因為[ShoppingCart]表中列[Week]沒有值1000,所以用PIVOT函數(shù)將列[Week]行轉(zhuǎn)列后列[1000]的值就為0。

?

如果將PIVOT函數(shù)中[行轉(zhuǎn)列后的列名]聲明為了[行轉(zhuǎn)列前的列名]完全不同的數(shù)據(jù)類型,還會導致PIVOT函數(shù)報錯,例如下面我們在[行轉(zhuǎn)列后的列名]中聲明了一個列名為字符串[TestColumnName],但是由于[行轉(zhuǎn)列前的列名]Week是Int類型,從而無法將字符串TestColumnName轉(zhuǎn)換為Int類型,所以PIVOT函數(shù)報錯了:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7],[TestColumnName])) AS T 

?

?

UNPIVOT函數(shù),列轉(zhuǎn)行

?

UNPIVOT函數(shù)的格式如下:

UNPIVOT([轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對應的列名] for [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對應的列名] in ([轉(zhuǎn)換為行的列1],[轉(zhuǎn)換為行的列2],[轉(zhuǎn)換為行的列3],...[轉(zhuǎn)換為行的列N]))
  • [轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對應的列名]這個是進行列轉(zhuǎn)行的列其數(shù)據(jù)值在轉(zhuǎn)換為行后的列名稱
  • [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對應的列名]這個是進行列轉(zhuǎn)行的列其列名在轉(zhuǎn)換為行后的列名稱
  • [轉(zhuǎn)換為行的列]這個是聲明哪些列要進行列轉(zhuǎn)行

如下所示,列轉(zhuǎn)行前為:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 

現(xiàn)在使用UNPIVOT函數(shù)將上面結果的列[1],[2],[3],[4],[5],[6],[7]轉(zhuǎn)換為行值,如下所示:

復制代碼
復制代碼
with PIVOT_Table as 
(
select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 
)

select * from PIVOT_Table UNPIVOT([RowCount] for [Week] in ([1],[2],[3],[4],[5],[6],[7])) as T
復制代碼
復制代碼

、

可以看到[1],[2],[3],[4],[5],[6],[7]這七列在UNPIVOT函數(shù)執(zhí)行后其值變?yōu)榱肆衃RowCount],列轉(zhuǎn)行前的列名稱在轉(zhuǎn)換后變?yōu)榱肆衃Week],同樣套用UNPIVOT函數(shù)格式我們可以得到如下結果:

  • [轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對應的列名]在本例中為[RowCount]
  • [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對應的列名]在本例中為[Week]
  • [轉(zhuǎn)換為行的列]這個是聲明哪些列要進行列轉(zhuǎn)行,在本例中為[1],[2],[3],[4],[5],[6],[7]這七列

?

需要注意如果列轉(zhuǎn)行前有兩行值:

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 

那么UNPIVOT函數(shù)轉(zhuǎn)換后應該為14行(列轉(zhuǎn)行前的行數(shù)2?X 需要進行列轉(zhuǎn)行的列數(shù)7 = 14)數(shù)據(jù):

復制代碼
復制代碼
with PIVOT_Table as 
(
select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 
)

select * from PIVOT_Table UNPIVOT([RowCount] for [Week] in ([1],[2],[3],[4],[5],[6],[7])) as T
復制代碼
復制代碼

?

此外需要注意UNPIVOT函數(shù)不會對列轉(zhuǎn)行中沒有用到的列作Group By處理,也不會對列傳行后的值做聚合運算,這一點是和PIVOT函數(shù)不同的。比如現(xiàn)在如果我們有下面一個查詢:

復制代碼
復制代碼
with PIVOT_Table as 
(
select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 
union all
select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 
)

select * from PIVOT_Table
復制代碼
復制代碼

起查詢結果為:

?

我們可以看到查詢結果中有兩行GroupId為1的數(shù)據(jù),現(xiàn)在我們再用UNPIVOT函數(shù)對這個查詢的列[1]到[7]做列轉(zhuǎn)行運算,其中沒有用到列GroupId:

復制代碼
復制代碼
with PIVOT_Table as 
(
select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 
union all
select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 
)

select * from PIVOT_Table UNPIVOT([RowCount] for [Week] in ([1],[2],[3],[4],[5],[6],[7])) as T
復制代碼
復制代碼

結果如下所示:

我們可以看到結果出現(xiàn)了14行數(shù)據(jù)(列轉(zhuǎn)行前的行數(shù)2?X 需要進行列轉(zhuǎn)行的列數(shù)7 = 14),所以我們可以看到雖然我們在UNPIVOT函數(shù)中沒有用到列GroupId,并且在列轉(zhuǎn)行前GroupId列有兩行相同的值1,但是UNPIVOT函數(shù)在列轉(zhuǎn)行后仍然生成了14行數(shù)據(jù),而不是7行數(shù)據(jù),因此并沒有對GroupId列做Group By處理來合并相同的值,這一點和前面的PIVOT函數(shù)是不同的。

原文鏈接:https://www.cnblogs.com/net-study/p/10396368.html

本文摘自 :https://www.cnblogs.com/

開通會員,享受整站包年服務立即開通 >