當前位置：首頁 > IT技術 > 數據庫 > 正文

sqlserver 行列轉換
2021-09-03 20:18:19

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

?

PIVOT函數，行轉列

?

PIVOT函數的格式如下

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

?

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

復制代碼
復制代碼
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
復制代碼
復制代碼

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

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

查詢結果如下：

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

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

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

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

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

?

需要注意的是PIVOT函數的查詢結果中多了一列GroupId，這是因為PIVOT函數只用到了[ShoppingCart]表中的列[Week]和[TotalPrice]，[ShoppingCart]表中還有一列[GroupId]，PIVOT函數沒有用到，所以PIVOT函數默認將[ShoppingCart]表中沒有用到的列當做了Group By來處理，用來作為行轉列后每一行數據分行的依據，又由于列[GroupId]在[ShoppingCart]表中全為值1，所以最后PIVOT函數在行轉列后只有一行[GroupId]為1的數據，如果我們將[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函數做行轉列后得到了兩行值，可以看到轉換后列[3]和[4]在[GroupId]為2的這一行上為1，這就是因為我們將[ShoppingCart]表中[Week]為3和4的兩行改成了[GroupId]為2后，[GroupId]有了兩個值1和2，所以PIVOT函數行轉列后就有兩行值。

?

知道了PIVOT函數的用法之后，我們來看看PIVOT函數的幾種錯誤用法：

在PIVOT函數的使用中有一點需要注意，那就是[行轉列后的列名]必須是[行轉列前的列名]的值，PIVOT函數才能成功執(zhí)行，比如如下所示如果我們將[行轉列后的列名]聲明了一個和[行轉列前的列名]值毫不相干的數字1000，那么PIVOT函數執(zhí)行后1000是沒有任何數據的為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函數將列[Week]行轉列后列[1000]的值就為0。

?

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

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

?

?

UNPIVOT函數，列轉行

?

UNPIVOT函數的格式如下：

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

如下所示，列轉行前為：

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

現在使用UNPIVOT函數將上面結果的列[1],[2],[3],[4],[5],[6],[7]轉換為行值，如下所示：

復制代碼
復制代碼
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函數執(zhí)行后其值變?yōu)榱肆衃RowCount]，列轉行前的列名稱在轉換后變?yōu)榱肆衃Week]，同樣套用UNPIVOT函數格式我們可以得到如下結果：

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

?

需要注意如果列轉行前有兩行值：

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

那么UNPIVOT函數轉換后應該為14行（列轉行前的行數2?X 需要進行列轉行的列數7 = 14）數據：

復制代碼
復制代碼
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函數不會對列轉行中沒有用到的列作Group By處理，也不會對列傳行后的值做聚合運算，這一點是和PIVOT函數不同的。比如現在如果我們有下面一個查詢：

復制代碼
復制代碼
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的數據，現在我們再用UNPIVOT函數對這個查詢的列[1]到[7]做列轉行運算，其中沒有用到列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
復制代碼
復制代碼

結果如下所示：

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

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

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

開通會員，享受整站包年服務
推薦內容