您所在的位置：首页 > 资讯 > 网络资讯 > SQL PIVOT函数：解锁数据透视表的强大功能，实战案例详解！

SQL PIVOT函数：解锁数据透视表的强大功能，实战案例详解！

编辑：佚名 2025-03-06 16:51:03

sql的pivot函数是一种强大的工具，它允许用户将行数据转换为列数据，从而生成类似于excel中数据透视表的效果。这一功能在数据分析和报表生成中尤为实用，尤其是在需要将某些行数据作为列进行展示的情况下，例如将销售数据按照不同产品进行汇总，并将每个产品的销售额作为列显示。

pivot函数的基本语法

pivot函数的基本语法如下：

```sql

select

<非透视的列>,

[第一个透视的列] as <列名称>,

[第二个透视的列] as <列名称>,

...

[最后一个透视的列] as <列名称>

from

(<生成数据的select查询>) as <源查询的别名>

pivot

(

<聚合函数>(<要聚合的列>)

for

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

([第一个透视的列], [第二个透视的列], ... [最后一个透视的列])

) as <透视表的别名>

<可选的order by子句>;

```

- `<非透视的列>`：不需要进行聚合的列。

- `[第一个透视的列] as <列名称>`等：指定的列值作为新列的名称。

- `

`：要查询的表。

- ``：要进行聚合的列。

- ``：要作为新列的名称的列。

- `[第一个透视的列], [第二个透视的列], ... [最后一个透视的列]`：具体的列值。

实例详解

为了更好地理解pivot函数的使用，我们可以通过一个具体的例子进行说明。

假设我们有一个名为`shoppingcart`的临时数据表，用于记录购物车内商品的信息，包括购买者姓名（`name`）、商品类别（`category`）和总价（`totalprice`）。表结构如下：

```sql

create table shoppingcart(

[name] nvarchar(8) not null,

[category] nvarchar(8) not null,

[totalprice] decimal default(0) not null

);

```

并插入一些测试数据：

```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;

```

现在，我们想要将上述数据进行分类汇总，得到每个人对应类别的价格加总。这可以通过使用pivot函数来实现：

```sql

select *

from shoppingcart

pivot

(

sum([totalprice])

for [category]

in ([饼干],[果冻],[面包])

) as a;

```

执行上述查询后，我们将得到一个透视表，其中每个人的每种商品类别的总价将被汇总并显示为单独的列。

动态pivot行转列

在实际应用中，有时需要转换的列字段数量可能非常多，这时手动编写pivot语句可能会变得非常繁琐。为了解决这个问题，我们可以使用动态sql来生成pivot查询。

以下是一个动态pivot行转列的示例：

```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);

```

这段代码首先通过查询`shoppingcart`表来生成所有可能的类别列，并将它们拼接成一个字符串。然后，它使用这个字符串来动态构建一个pivot查询，并执行该查询。

注意事项

1. 兼容级别：pivot和unpivot是sql server 2005及更高版本的语法。在使用这些功能时，需要确保数据库的兼容级别设置为90或更高。可以通过以下查询来检查数据库的兼容级别：

```sql

select compatibility_level

from sys.databases

where name = ⁄'数据库名⁄';

```

如果需要修改兼容级别，可以使用以下命令：

```sql

alter database 数据库名 set compatibility_level = 90;

```

2. null值处理：pivot函数不会输出值为null的数据行。因此，在将列转换回行（使用unpivot函数）时，也无法复原原始数据中原有的null值。

3. 分组问题：在使用pivot函数时，需要注意分组列的选择。pivot函数只能指定需要横向显示的列（即聚合函数中的列和要成为列的值的列），不能明确定义分组列。用作分组的列是pivot函数中没有应用到的列，它们会根据这些列和透视列的组合进行分组。

通过掌握pivot函数的基本语法和注意事项，并结合实例进行练习，我们可以更加高效地处理和分析数据。

更多 >