您所在的位置：首頁(yè) > 資訊 > 網(wǎng)絡(luò)資訊 > SQL PIVOT函數(shù)：解鎖數(shù)據(jù)透視表的強(qiáng)大功能，實(shí)戰(zhàn)案例詳解！

SQL PIVOT函數(shù)：解鎖數(shù)據(jù)透視表的強(qiáng)大功能，實(shí)戰(zhàn)案例詳解！

編輯：佚名 2025-03-06 16:51:03

sql的pivot函數(shù)是一種強(qiáng)大的工具，它允許用戶將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)，從而生成類似于excel中數(shù)據(jù)透視表的效果。這一功能在數(shù)據(jù)分析和報(bào)表生成中尤為實(shí)用，尤其是在需要將某些行數(shù)據(jù)作為列進(jìn)行展示的情況下，例如將銷售數(shù)據(jù)按照不同產(chǎn)品進(jìn)行匯總，并將每個(gè)產(chǎn)品的銷售額作為列顯示。

pivot函數(shù)的基本語(yǔ)法

pivot函數(shù)的基本語(yǔ)法如下：

```sql

select

<非透視的列>,

[第一個(gè)透視的列] as <列名稱>,

[第二個(gè)透視的列] as <列名稱>,

...

[最后一個(gè)透視的列] as <列名稱>

from

(<生成數(shù)據(jù)的select查詢>) as <源查詢的別名>

pivot

(

<聚合函數(shù)>(<要聚合的列>)

for

[<包含要成為列的值的列>]

([第一個(gè)透視的列], [第二個(gè)透視的列], ... [最后一個(gè)透視的列])

) as <透視表的別名>

<可選的order by子句>;

```

- `<非透視的列>`：不需要進(jìn)行聚合的列。

- `[第一個(gè)透視的列] as <列名稱>`等：指定的列值作為新列的名稱。

- `

`：要查詢的表。

- ``：要進(jìn)行聚合的列。

- ``：要作為新列的名稱的列。

- `[第一個(gè)透視的列], [第二個(gè)透視的列], ... [最后一個(gè)透視的列]`：具體的列值。

實(shí)例詳解

為了更好地理解pivot函數(shù)的使用，我們可以通過(guò)一個(gè)具體的例子進(jìn)行說(shuō)明。

假設(shè)我們有一個(gè)名為`shoppingcart`的臨時(shí)數(shù)據(jù)表，用于記錄購(gòu)物車內(nèi)商品的信息，包括購(gòu)買者姓名（`name`）、商品類別（`category`）和總價(jià)（`totalprice`）。表結(jié)構(gòu)如下：

```sql

create table shoppingcart(

[name] nvarchar(8) not null,

[category] nvarchar(8) not null,

[totalprice] decimal default(0) not null

);

```

并插入一些測(cè)試數(shù)據(jù)：

```sql

insert into shoppingcart([name],[category],[totalprice])

select ⁄'張三⁄',⁄'餅干⁄',30 union all

select ⁄'張三⁄',⁄'面包⁄',10 union all

select ⁄'張三⁄',⁄'果凍⁄',30 union all

select ⁄'李四⁄',⁄'餅干⁄',40 union all

select ⁄'李四⁄',⁄'面包⁄',20 union all

select ⁄'李四⁄',⁄'果凍⁄',20 union all

select ⁄'陳小二⁄',⁄'餅干⁄',20 union all

select ⁄'陳小二⁄',⁄'餅干⁄',50 union all

select ⁄'陳小二⁄',⁄'面包⁄',30 union all

select ⁄'陳小二⁄',⁄'果凍⁄',30;

```

現(xiàn)在，我們想要將上述數(shù)據(jù)進(jìn)行分類匯總，得到每個(gè)人對(duì)應(yīng)類別的價(jià)格加總。這可以通過(guò)使用pivot函數(shù)來(lái)實(shí)現(xiàn)：

```sql

select *

from shoppingcart

pivot

(

sum([totalprice])

for [category]

in ([餅干],[果凍],[面包])

) as a;

```

執(zhí)行上述查詢后，我們將得到一個(gè)透視表，其中每個(gè)人的每種商品類別的總價(jià)將被匯總并顯示為單獨(dú)的列。

動(dòng)態(tài)pivot行轉(zhuǎn)列

在實(shí)際應(yīng)用中，有時(shí)需要轉(zhuǎn)換的列字段數(shù)量可能非常多，這時(shí)手動(dòng)編寫(xiě)pivot語(yǔ)句可能會(huì)變得非常繁瑣。為了解決這個(gè)問(wèn)題，我們可以使用動(dòng)態(tài)sql來(lái)生成pivot查詢。

以下是一個(gè)動(dòng)態(tài)pivot行轉(zhuǎn)列的示例：

```sql

declare @sql_str varchar(8000);

declare @sql_col varchar(8000);

select @sql_col = isnull(@sql_col + ⁄',⁄', ⁄'⁄') + quotename([category])

from shoppingcart

group by [category];

set @sql_str = ⁄'

select *

from shoppingcart

pivot

(

sum([totalprice])

for [category]

in (⁄' + @sql_col + ⁄')

) as pvt⁄';

exec (@sql_str);

```

這段代碼首先通過(guò)查詢`shoppingcart`表來(lái)生成所有可能的類別列，并將它們拼接成一個(gè)字符串。然后，它使用這個(gè)字符串來(lái)動(dòng)態(tài)構(gòu)建一個(gè)pivot查詢，并執(zhí)行該查詢。

注意事項(xiàng)

1. 兼容級(jí)別：pivot和unpivot是sql server 2005及更高版本的語(yǔ)法。在使用這些功能時(shí)，需要確保數(shù)據(jù)庫(kù)的兼容級(jí)別設(shè)置為90或更高?？梢酝ㄟ^(guò)以下查詢來(lái)檢查數(shù)據(jù)庫(kù)的兼容級(jí)別：

```sql

select compatibility_level

from sys.databases

where name = ⁄'數(shù)據(jù)庫(kù)名⁄';

```

如果需要修改兼容級(jí)別，可以使用以下命令：

```sql

alter database 數(shù)據(jù)庫(kù)名 set compatibility_level = 90;

```

2. null值處理：pivot函數(shù)不會(huì)輸出值為null的數(shù)據(jù)行。因此，在將列轉(zhuǎn)換回行（使用unpivot函數(shù)）時(shí)，也無(wú)法復(fù)原原始數(shù)據(jù)中原有的null值。

3. 分組問(wèn)題：在使用pivot函數(shù)時(shí)，需要注意分組列的選擇。pivot函數(shù)只能指定需要橫向顯示的列（即聚合函數(shù)中的列和要成為列的值的列），不能明確定義分組列。用作分組的列是pivot函數(shù)中沒(méi)有應(yīng)用到的列，它們會(huì)根據(jù)這些列和透視列的組合進(jìn)行分組。