1422 字

7 分钟

SqlServer 中行转列PIVOT函数用法

2025-03-20

数据库

Sql

/

Sql Server

SqlServer 中行转列PIVOT函数用法#

前言#

最近在面试的时候，碰到了手写sql的题目，这让我这个面向AI的程序员着实难看。只见我面露难色，绞尽脑汁的情况下，终于还是放弃了。

这道题目不难，但是由于平时几乎没有遇到行转列的情况，导致在手写时忘记了PIVOT函数怎么使用😩。

面试准备不充分给自己找借口，菜就多练，不会写就别写。

题目描述#

下面请看题：

假设有以下表 EmpCanlendar：

Name	CalendarDate	ClassName
张三	2005-05-01	日班
张三	2005-05-02	日班
张三	2005-05-03	夜班
李四	2005-05-01	夜班
李四	2005-05-02	日班
…	…	…

输出结果：

Name	D20050501	D20050502	D20050503	D20050504	D20050505
张三	日班	日班	日班	日班	夜班
李四	日班	日班	日班	NULL	NULL
王五	NULL	夜班	夜班	NULL	NULL

`PIVOT`函数简单介绍#

PIVOT 是 SQL Server 中的一种功能，用于将行数据转换为列数据（即行转列）。它通常用于将某一列的唯一值作为新列，并将对应的值填充到这些新列中。PIVOT 是数据透视表的一种实现方式，非常适合用于统计和报表场景。

`PIVOT` 的基本语法#

1
SELECT
2
    [非透视列],
3
    [透视值1], [透视值2], ..., [透视值N]
4
FROM
5
    (
6
        -- 子查询：提供原始数据
7
        SELECT [非透视列], [透视列], [值列]
8
        FROM 表名
9
    ) AS 源表
10
PIVOT
11
(
12
    聚合函数(值列) -- 例如 SUM、COUNT、MAX 等
13
    FOR 透视列 IN ([透视值1], [透视值2], ..., [透视值N])
14
) AS 透视表

参数#

非透视列：
- 不需要转换的列，这些列的值将作为结果表的行标识。
透视列：
- 需要转换为新列的列。
值列：
- 需要填充到新列中的值。
聚合函数：
- 对值列进行聚合操作，例如 SUM、COUNT、MAX 等。
- 如果值列不需要聚合，可以使用 MAX 或 MIN。
透视值：
- 透视列中的唯一值，这些值将成为新列的名称。

解题#

测试数据准备#

1
CREATE TABLE #EmpCanlendar(
2
  [Name] NVARCHAR(20) NULL,
3
  CalendarDate DATETIME NULL,
4
  ClassName NVARCHAR(10) NULL,
5
)
6
INSERT INTO #EmpCanlendar(Name,CalendarDate,ClassName)
7
VALUES
8
('张三','2005-05-01','日班'),
9
('张三','2005-05-02','日班'),
10
('张三','2005-05-03','日班'),
11
('张三','2005-05-04','日班'),
12
('张三','2005-05-05','夜班'),
13
('李四','2005-05-01','日班'),
14
('李四','2005-05-02','日班'),
15
('李四','2005-05-03','日班'),
16
('王五','2005-05-02','夜班'),
17
('王五','2005-05-03','夜班')

非动态`sql`#

适用于日期范围固定，或者日期列较少的情况

1
SELECT
2
    Name,
3
    [2005-05-01] AS D20050501,
4
    [2005-05-02] AS D20050502,
5
    [2005-05-03] AS D20050503,
6
    [2005-05-04] AS D20050504,
7
    [2005-05-05] AS D20050505
8
FROM
9
(
10
    SELECT
11
        Name,
12
        CalendarDate,
13
        ClassName
14
    FROM
15
        #EmpCanlendar
16
) AS SourceTable
17
PIVOT
18
(
19
    MAX(ClassName)
20
    FOR CalendarDate IN ([2005-05-01], [2005-05-02], [2005-05-03], [2005-05-04], [2005-05-05])
21
) AS PivotTable;

查询结果

动态`sql`#

适用于日期范围不固定，或者日期列较多的情况

SQL Server 2017及以上版本：

1
-- 声明变量存储动态 SQL
2
DECLARE @sql NVARCHAR(MAX) = ''
3
DECLARE @columns NVARCHAR(MAX) = ''
4

5
SELECT @columns = STRING_AGG(QUOTENAME('D' + CONVERT(VARCHAR, DistinctDates.CalendarDate, 112)), ',')
6
FROM (SELECT DISTINCT CalendarDate FROM #EmpCanlendar) AS DistinctDates
7

8
SET @sql = '
9
  SELECT
10
    Name , ' + @columns +'
11
  FROM
12
  (
13
    SELECT
14
      Name,
15
      ''D'' + CONVERT(VARCHAR,CalendarDate, 112) CalendarDate,
16
      ClassName
17
    FROM
18
      #EmpCanlendar
19
  ) AS SourceTable
20
  PIVOT
21
  (
22
    MAX(ClassName)
23
    FOR CalendarDate IN ('+ @columns +')
24
  ) AS PivotTable;
25
'
26
PRINT @sql
27
EXEC sp_executesql @sql

SQL Server 2017以下版本

1
SELECT @columns = STUFF(
2
    (
3
        SELECT DISTINCT ',' + QUOTENAME('D'+ CONVERT(VARCHAR, CalendarDate, 112))
4
        FROM #EmpCanlendar
5
        FOR XML PATH('')
6
    ),1, 1, ''
7
)

查询结果：

扩展#

`STUFF`函数简单介绍#

STUFF 是 SQL Server 中的一个字符串函数，用于删除字符串的一部分并在指定位置插入新的子字符串。它的主要作用是修改字符串的内容，通常用于拼接或替换字符串中的某些部分。

`STUFF` 函数的语法：#

1
STUFF(原始字符串, 开始位置, 删除长度, 新子字符串)

参数#

原始字符串：
- 需要修改的字符串。
- 可以是字符型（CHAR、VARCHAR、NVARCHAR 等）的列、变量或表达式。
开始位置：
- 指定从原始字符串的哪个位置开始删除和插入。
- 位置从 1 开始计数。
删除长度：
- 指定要删除的字符数。
- 如果为 0，则不删除任何字符，仅插入新子字符串。
- 如果大于原始字符串的长度，则从开始位置删除到字符串末尾。
新子字符串：
- 要插入的新字符串。
- 如果为空字符串 ''，则仅删除字符，不插入新内容。

可用于#

删除字符：从指定位置开始删除一定长度的字符。
插入字符：在删除字符的位置插入新的子字符串。
替换字符：通过删除和插入操作，可以实现字符串的替换。

`STRING_AGG`函数简单介绍：#

STRING_AGG 是 SQL Server 2017 及更高版本中引入的一个聚合函数，用于将一组字符串值连接成一个单独的字符串，并使用指定的分隔符分隔每个值。它是 SQL Server 中处理字符串拼接的强大工具，特别适合将多行数据合并为一个字符串。

`STRING_AGG` 的基本语法#

1
STRING_AGG(表达式, 分隔符)

参数#

表达式：
- 需要拼接的字符串列或表达式。
- 可以是 VARCHAR、NVARCHAR、CHAR 等字符类型。
分隔符：
- 用于分隔每个字符串值的字符或字符串。
- 可以是任意字符串，例如 ,、;、- 等。

`QUOTENAME`函数简单介绍#

在SQL Server中，QUOTENAME()函数用于将一个标识符（如表名、列名等）包围在方括号中，以防止引起语法错误或与关键字冲突。

`QUOTENAME`函数的语法：#

1
QUOTENAME ( 'character_string' [ , 'quote_character' ] )

参数#

'character_string'：是要包围的标识符，可以是表名、列名等

'quote_character'：是可选参数，用于指定用于包围标识符的字符，默认为方括号（[ ]）。

参考链接#

深入浅出：SQL Server 中的 PIVOT 与 UNPIVOT 用法详解：https://blog.csdn.net/houbincarson/article/details/145483265
QUOTENAME (Transact-SQL) ：https://learn.microsoft.com/zh-cn/sql/t-sql/functions/quotename-transact-sql?view=sql-server-ver16
SQL Server中quotename()函数怎么使用：https://www.cnblogs.com/luyj00436/p/18453443

SqlServer 中行转列PIVOT函数用法

https://blog.pljzy.top/posts/pivot/sqlserver-中行转列pivot函数用法/

作者

ZyPlj

发布于

2025-03-20

许可协议

CC BY-NC-SA 4.0

程序猿等于装机猿吗？

关于我用Claude 3.7 Sonnet模型直接生成小程序

1

SqlServer 中行转列PIVOT函数用法

SqlServer 中行转列PIVOT函数用法#

前言#

题目描述#

PIVOT函数简单介绍#

PIVOT 的基本语法#

参数#

解题#

测试数据准备#

非动态sql#

动态sql#

扩展#

STUFF函数简单介绍#

STUFF 函数的语法：#

参数#

可用于#

STRING_AGG函数简单介绍：#

STRING_AGG 的基本语法#

参数#

QUOTENAME函数简单介绍#

QUOTENAME函数的语法：#

参数#

参考链接#

`PIVOT`函数简单介绍#

`PIVOT` 的基本语法#

非动态`sql`#

动态`sql`#

`STUFF`函数简单介绍#

`STUFF` 函数的语法：#

`STRING_AGG`函数简单介绍：#

`STRING_AGG` 的基本语法#

`QUOTENAME`函数简单介绍#

`QUOTENAME`函数的语法：#