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

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

我們在寫Sql語句的時候沒經(jīng)常會遇到將查詢結(jié)果行轉(zhuǎn)列，列轉(zhuǎn)行的需求，拼接sql字符串，然后使用sp_executesql執(zhí)行sql字符串是比較常規(guī)的一種做法。但是這樣做實現(xiàn)起來非常復(fù)雜，而在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)列后計算列的聚合方式。
  • [聚合列值]要進(jì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ù)和表結(jié)構(gòu)如下所示：

復(fù)制代碼
復(fù)制代碼
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
復(fù)制代碼
復(fù)制代碼

現(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

查詢結(jié)果如下：

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

  • <聚合函數(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)換后的列名也為七個，對應(yīng)這七個值分別為[1],[2],[3],[4],[5],[6],[7]，PIVOT函數(shù)會將[ShoppingCart]表中[Week]列的值分別和[1],[2],[3],[4],[5],[6],[7]這七列的列名進(jìn)行匹配，然后計算<聚合函數(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

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

?

需要注意的是PIVOT函數(shù)的查詢結(jié)果中多了一列GroupId，這是因為PIVOT函數(shù)只用到了[ShoppingCart]表中的列[Week]和[TotalPrice]，[ShoppingCart]表中還有一列[GroupId]，PIVOT函數(shù)沒有用到，所以PIVOT函數(shù)默認(rèn)將[ShoppingCart]表中沒有用到的列當(dāng)做了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

會得到如下結(jié)果：

我們看到這一次我們用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ù)類型，還會導(dǎo)致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)換后對應(yīng)的列名] for [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對應(yīng)的列名] in ([轉(zhuǎn)換為行的列1],[轉(zhuǎn)換為行的列2],[轉(zhuǎn)換為行的列3],...[轉(zhuǎn)換為行的列N]))
  • [轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對應(yīng)的列名]這個是進(jìn)行列轉(zhuǎn)行的列其數(shù)據(jù)值在轉(zhuǎn)換為行后的列名稱
  • [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對應(yīng)的列名]這個是進(jìn)行列轉(zhuǎn)行的列其列名在轉(zhuǎn)換為行后的列名稱
  • [轉(zhuǎn)換為行的列]這個是聲明哪些列要進(jì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ù)將上面結(jié)果的列[1],[2],[3],[4],[5],[6],[7]轉(zhuǎn)換為行值，如下所示：

復(fù)制代碼
復(fù)制代碼
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
復(fù)制代碼
復(fù)制代碼

、

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

  • [轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對應(yīng)的列名]在本例中為[RowCount]
  • [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對應(yīng)的列名]在本例中為[Week]
  • [轉(zhuǎn)換為行的列]這個是聲明哪些列要進(jì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)換后應(yīng)該為14行（列轉(zhuǎn)行前的行數(shù)2?X 需要進(jìn)行列轉(zhuǎn)行的列數(shù)7 = 14）數(shù)據(jù)：

復(fù)制代碼
復(fù)制代碼
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
復(fù)制代碼
復(fù)制代碼

?

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

復(fù)制代碼
復(fù)制代碼
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
復(fù)制代碼
復(fù)制代碼

起查詢結(jié)果為：

?

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

復(fù)制代碼
復(fù)制代碼
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
復(fù)制代碼
復(fù)制代碼

結(jié)果如下所示：

我們可以看到結(jié)果出現(xiàn)了14行數(shù)據(jù)（列轉(zhuǎn)行前的行數(shù)2?X 需要進(jìn)行列轉(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/

開通會員，享受整站包年服務(wù)立即開通 >
推薦內(nèi)容