SQL PIVOT函数实战指南与案例解析

在sql中，pivot函数是一种强大的数据转换工具，它能够将行数据转换为列数据，实现类似于excel中数据透视表的效果。通过pivot函数，用户可以更方便地对数据进行聚合和分析。本文将详细介绍sql pivot函数的使用方法和实例，帮助用户全面了解这一功能。

pivot函数的基本语法

pivot函数的基本语法如下：

```sql

select

<非透视的列>,

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

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

...

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

from

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

pivot

(

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

for

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

in

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

) as <透视表的别名>

<可选的order>;

```

pivot函数的工作原理

1. 数据准备：首先，通过子查询生成需要转换的数据集。

2. 分组：根据pivot函数中的分组列对数据进行分组。

3. 透视：将分组后的数据按照指定的列进行透视，将每个聚合值作为一列。

4. 聚合：使用聚合函数（如sum、avg等）对透视后的数据进行计算。

实例详解

实例一：购物数据分类汇总

假设有一张购物数据表`shoppingcart`，包含购物者姓名（name）、商品类别（category）和总价（totalprice）。现在需要将数据按购物者姓名分类，并汇总每个购物者在每个商品类别上的总花费。

```sql

-- 创建测试数据表

create table shoppingcart (

[name] nvarchar(8) not null,

[category] nvarchar(8) not null,

[totalprice] decimal default(0) not null

);

-- 插入测试数据

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

values

(⁄'张三⁄', ⁄'饼干⁄', 30),

(⁄'张三⁄', ⁄'面包⁄', 10),

(⁄'张三⁄', ⁄'果冻⁄', 30),

(⁄'李四⁄', ⁄'饼干⁄', 40),

(⁄'李四⁄', ⁄'面包⁄', 20),

(⁄'李四⁄', ⁄'果冻⁄', 20),

(⁄'陈小二⁄', ⁄'饼干⁄', 20),

(⁄'陈小二⁄', ⁄'饼干⁄', 50),

(⁄'陈小二⁄', ⁄'面包⁄', 30),

(⁄'陈小二⁄', ⁄'果冻⁄', 30);

-- 使用pivot函数进行分类汇总

select

*

from

shoppingcart

pivot

(

sum([totalprice])

for [category]

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

) as pvt;

```

执行上述查询后，将得到一张透视表，其中每个购物者的姓名作为一行，每个商品类别作为一列，列中的值为该购物者在对应类别上的总花费。

实例二：学生成绩报表生成

假设有一张学生成绩表`tb_student_score`，包含学生姓名（student_name）、学科（student_subject）和成绩（student_score）。现在需要生成一张报表，显示每个学生的各科成绩、总分和平均分。

```sql

-- 使用pivot函数进行行列转换

select

student_name,

cet.[a],

cet.[b],

cet.[c],

cet.[d]

from

tb_student_score

pivot

(

avg(student_score)

for student_subject

in ([a], [b], [c], [d])

) as cet;

-- 使用开窗函数计算总分和平均分

select

t1.*,

avg(t2.student_score) over (partition by t2.student_name) as ⁄'平均成绩⁄',

sum(t2.student_score) over (partition by t2.student_name) as ⁄'总分⁄'

from

(

select

student_name,

cet.[a],

cet.[b],

cet.[c],

cet.[d]

from

tb_student_score

pivot

(

avg