SqlServer 中行轉列PIVOT函數用法

前言

最近在面試的時候，碰到了手寫sql的題目，這讓我這個面向AI的程序員著實難看。只見我面露難色，絞盡腦汁的情況下，終于還是放棄了。

這道題目不難，但是由于平時幾乎沒有遇到行轉列的情況，導致在手寫時忘記了PIVOT函數怎么使用??。

面試準備不充分給自己找借口，菜就多練，不會寫就別寫。

題目描述

下面請看題：

假設有以下表 EmpCanlendar：

輸出結果：

PIVOT函數簡單介紹

PIVOT 是 SQL Server 中的一種功能，用于將行數據轉換為列數據（即行轉列）。它通常用于將某一列的唯一值作為新列，并將對應的值填充到這些新列中。PIVOT 是數據透視表的一種實現方式，非常適合用于統計和報表場景。

PIVOT 的基本語法

SELECT 
    [非透視列],
    [透視值1], [透視值2], ..., [透視值N]
FROM 
    (
        -- 子查詢：提供原始數據
        SELECT [非透視列], [透視列], [值列]
        FROM 表名
    ) AS 源表
PIVOT 
(
    聚合函數(值列) -- 例如 SUM、COUNT、MAX 等
    FOR 透視列 IN ([透視值1], [透視值2], ..., [透視值N])
) AS 透視表

參數

1. 非透視列：
   • 不需要轉換的列，這些列的值將作為結果表的行標識。
2. 透視列：
   • 需要轉換為新列的列。
3. 值列：
   • 需要填充到新列中的值。
4. 聚合函數：
   • 對值列進行聚合操作，例如 SUM、COUNT、MAX 等。
   • 如果值列不需要聚合，可以使用 MAX 或 MIN。
5. 透視值：
   • 透視列中的唯一值，這些值將成為新列的名稱。

解題

測試數據準備

CREATE TABLE #EmpCanlendar(
	[Name] NVARCHAR(20) NULL,
	CalendarDate DATETIME NULL,
	ClassName NVARCHAR(10) NULL,
)
INSERT INTO #EmpCanlendar(Name,CalendarDate,ClassName)
VALUES
('張三','2005-05-01','日班'),
('張三','2005-05-02','日班'),
('張三','2005-05-03','日班'),
('張三','2005-05-04','日班'),
('張三','2005-05-05','夜班'),
('李四','2005-05-01','日班'),
('李四','2005-05-02','日班'),
('李四','2005-05-03','日班'),
('王五','2005-05-02','夜班'),
('王五','2005-05-03','夜班')

非動態sql

適用于日期范圍固定，或者日期列較少的情況

SELECT 
    Name,
    [2005-05-01] AS D20050501,
    [2005-05-02] AS D20050502,
    [2005-05-03] AS D20050503,
    [2005-05-04] AS D20050504,
    [2005-05-05] AS D20050505
FROM 
(
    SELECT 
        Name, 
        CalendarDate, 
        ClassName
    FROM 
        #EmpCanlendar
) AS SourceTable
PIVOT
(
    MAX(ClassName)
    FOR CalendarDate IN ([2005-05-01], [2005-05-02], [2005-05-03], [2005-05-04], [2005-05-05])
) AS PivotTable;

查詢結果

動態sql

適用于日期范圍不固定，或者日期列較多的情況

SQL Server 2017及以上版本：

-- 聲明變量存儲動態 SQL
DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @columns NVARCHAR(MAX) = ''

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

SET @sql = '
	SELECT 
		Name , ' + @columns +'
	FROM
	(
		SELECT 
			Name,
			''D'' + CONVERT(VARCHAR,CalendarDate, 112) CalendarDate,
			ClassName
		FROM
			#EmpCanlendar
	) AS SourceTable
	PIVOT
	(
		MAX(ClassName)
		FOR CalendarDate IN ('+ @columns +')
	) AS PivotTable;
'
PRINT @sql
EXEC sp_executesql @sql

SQL Server 2017以下版本

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

查詢結果：

擴展

STUFF函數簡單介紹

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

STUFF 函數的語法：

STUFF(原始字符串, 開始位置, 刪除長度, 新子字符串)

參數

1. 原始字符串：
   • 需要修改的字符串。
   • 可以是字符型（CHAR、VARCHAR、NVARCHAR 等）的列、變量或表達式。
2. 開始位置：
   • 指定從原始字符串的哪個位置開始刪除和插入。
   • 位置從 1 開始計數。
3. 刪除長度：
   • 指定要刪除的字符數。
   • 如果為 0，則不刪除任何字符，僅插入新子字符串。
   • 如果大于原始字符串的長度，則從開始位置刪除到字符串末尾。
4. 新子字符串：
   • 要插入的新字符串。
   • 如果為空字符串 ''，則僅刪除字符，不插入新內容。

可用于

• 刪除字符：從指定位置開始刪除一定長度的字符。
• 插入字符：在刪除字符的位置插入新的子字符串。
• 替換字符：通過刪除和插入操作，可以實現字符串的替換。

STRING_AGG函數簡單介紹：

STRING_AGG 是 SQL Server 2017 及更高版本中引入的一個聚合函數，用于將一組字符串值連接成一個單獨的字符串，并使用指定的分隔符分隔每個值。它是 SQL Server 中處理字符串拼接的強大工具，特別適合將多行數據合并為一個字符串。

STRING_AGG 的基本語法

STRING_AGG(表達式, 分隔符)

參數

1. 表達式：
   • 需要拼接的字符串列或表達式。
   • 可以是 VARCHAR、NVARCHAR、CHAR 等字符類型。
2. 分隔符：
   • 用于分隔每個字符串值的字符或字符串。
   • 可以是任意字符串，例如 ,、;、- 等。

QUOTENAME函數簡單介紹

在SQL Server中，QUOTENAME()函數用于將一個標識符（如表名、列名等）包圍在方括號中，以防止引起語法錯誤或與關鍵字沖突。

QUOTENAME函數的語法：

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()函數怎么使用：http://www.rzrgm.cn/luyj00436/p/18453443