欧美日韩国产高清在线视频,影音先锋人妻啪啪AV资源网站,国产xvideos国产在线

我們?cè)趯?xiě)Sql語(yǔ)句的時(shí)候沒(méi)經(jīng)常會(huì)遇到將查詢結(jié)果行轉(zhuǎn)列，列轉(zhuǎn)行的需求，拼接sql字符串，然后使用sp_executesql執(zhí)行sql字符串是比較常規(guī)的一種做法。但是這樣做實(shí)現(xiàn)起來(lái)非常復(fù)雜，而在SqlServer2005中我們有了PIVOT/UNPIVOT函數(shù)可以快速實(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ì)算列的聚合方式。
[聚合列值]要進(jìn)行聚合的列名
[行轉(zhuǎn)列前的列名]這個(gè)就是需要將行轉(zhuǎn)換為列的列名。
[行轉(zhuǎn)列后的列名]這里需要聲明將行的值轉(zhuǎn)換為列后的列名，因?yàn)檗D(zhuǎn)換后的列名其實(shí)就是轉(zhuǎn)換前行的值，所以上面格式中的[行轉(zhuǎn)列后的列名1],[行轉(zhuǎn)列后的列名2],[行轉(zhuǎn)列后的列名3],......[行轉(zhuǎn)列后的列名N]其實(shí)就是[行轉(zhuǎn)列前的列名]每一行的值。

下面我們來(lái)看一個(gè)例子有一張表名為[ShoppingCart]有三列[Week],[TotalPrice],[GroupId]，數(shù)據(jù)和表結(jié)構(gòu)如下所示：

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)來(lái)統(tǒng)計(jì)每一個(gè)Week列在轉(zhuǎn)換前有多少行數(shù)據(jù)，語(yǔ)句如下所示：

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)計(jì)轉(zhuǎn)換前的行數(shù)為1，這符合我們的預(yù)期結(jié)果。那么根據(jù)我們前面定義的PIVOT函數(shù)轉(zhuǎn)換格式，在本例中我們有如下公式對(duì)應(yīng)值:

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

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

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

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

需要注意的是PIVOT函數(shù)的查詢結(jié)果中多了一列GroupId，這是因?yàn)镻IVOT函數(shù)只用到了[ShoppingCart]表中的列[Week]和[TotalPrice]，[ShoppingCart]表中還有一列[GroupId]，PIVOT函數(shù)沒(méi)有用到，所以PIVOT函數(shù)默認(rèn)將[ShoppingCart]表中沒(méi)有用到的列當(dāng)做了Group By來(lái)處理，用來(lái)作為行轉(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

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

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

知道了PIVOT函數(shù)的用法之后，我們來(lái)看看PIVOT函數(shù)的幾種錯(cuò)誤用法：

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

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

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

如果將PIVOT函數(shù)中[行轉(zhuǎn)列后的列名]聲明為了[行轉(zhuǎn)列前的列名]完全不同的數(shù)據(jù)類型，還會(huì)導(dǎo)致PIVOT函數(shù)報(bào)錯(cuò)，例如下面我們?cè)赱行轉(zhuǎn)列后的列名]中聲明了一個(gè)列名為字符串[TestColumnName]，但是由于[行轉(zhuǎn)列前的列名]Week是Int類型，從而無(wú)法將字符串TestColumnName轉(zhuǎn)換為Int類型，所以PIVOT函數(shù)報(bào)錯(cuò)了：

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)換后對(duì)應(yīng)的列名] for [轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對(duì)應(yīng)的列名] in ([轉(zhuǎn)換為行的列1],[轉(zhuǎn)換為行的列2],[轉(zhuǎn)換為行的列3],...[轉(zhuǎn)換為行的列N]))

[轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對(duì)應(yīng)的列名]這個(gè)是進(jìn)行列轉(zhuǎn)行的列其數(shù)據(jù)值在轉(zhuǎn)換為行后的列名稱
[轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對(duì)應(yīng)的列名]這個(gè)是進(jìn)行列轉(zhuǎn)行的列其列名在轉(zhuǎn)換為行后的列名稱
[轉(zhuǎn)換為行的列]這個(gè)是聲明哪些列要進(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)換為行值，如下所示：

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ù)格式我們可以得到如下結(jié)果：

[轉(zhuǎn)換為行的列值在轉(zhuǎn)換后對(duì)應(yīng)的列名]在本例中為[RowCount]
[轉(zhuǎn)換為行的列名在轉(zhuǎn)換后對(duì)應(yīng)的列名]在本例中為[Week]
[轉(zhuǎn)換為行的列]這個(gè)是聲明哪些列要進(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ù)：

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ù)不會(huì)對(duì)列轉(zhuǎn)行中沒(méi)有用到的列作Group By處理，也不會(huì)對(duì)列傳行后的值做聚合運(yùn)算，這一點(diǎn)是和PIVOT函數(shù)不同的。比如現(xiàn)在如果我們有下面一個(gè)查詢：

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

起查詢結(jié)果為：

我們可以看到查詢結(jié)果中有兩行GroupId為1的數(shù)據(jù)，現(xiàn)在我們?cè)儆肬NPIVOT函數(shù)對(duì)這個(gè)查詢的列[1]到[7]做列轉(zhuǎn)行運(yùn)算，其中沒(méi)有用到列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

結(jié)果如下所示：

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

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

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

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

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