您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 進階行轉列與矩陣轉換實戰：從動態 PIVOT 到行為路徑分析

編輯：關於SqlServer

場景一：多值欄位動態行轉列

需求描述

原始表 TempTable_Base 記錄 GUID 與 Code 的多對一關係（圖1），需轉換為每個 GUID 一列，Code 值依序展開為多欄（圖2）。

原始資料結構目標行轉列結果

核心技術：ROW_NUMBER() 分組排序 + 動態 SQL PIVOT

-- 1. 建立測試資料
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('[TempTable_Base]') AND OBJECTPROPERTY(id,'IsUserTable')=1)
    DROP TABLE [TempTable_Base];

CREATE TABLE [TempTable_Base] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [guid] [varchar](50) NULL,
    [code] [varchar](50) NULL
);
-- 插入範例資料（略，見原文）

-- 2. 使用 ROW_NUMBER() 為同一 GUID 內的 Code 編號
SELECT base.*, ROW_NUMBER() OVER (PARTITION BY guid ORDER BY ID) AS depth
FROM [TempTable_Base] AS base
ORDER BY id;
-- 結果見圖3：每個 GUID 內 Code 獲得 1,2,3... 序號

-- 3. 建立中繼分組表
SELECT base.*, ROW_NUMBER() OVER (PARTITION BY guid ORDER BY ID) AS depth
INTO [TempTable_Depth]
FROM [TempTable_Base] AS base;

-- 4. 動態行轉列：未知欄數時自動生成 PIVOT SQL
DECLARE @s NVARCHAR(MAX) = '';
SELECT @s = @s + ',' + QUOTENAME([depth]) + '=MAX(CASE WHEN [depth]=' + QUOTENAME([depth],'''') + ' THEN code ELSE NULL END)'
FROM [TempTable_Depth] GROUP BY [depth] ORDER BY [depth];

EXEC('SELECT guid ' + @s + ' FROM [TempTable_Depth] GROUP BY guid ORDER BY guid');
-- 結果即圖2：每個 GUID 一列，Code_1, Code_2... 動態展開

場景二：行為路徑轉換矩陣（轉移頻率統計）

需求描述

將使用者瀏覽序列（A→C→E→O→G）轉換為狀態轉移矩陣，統計每對相鄰代碼出現次數（圖4）。

目標轉移矩陣

四步驟實現邏輯

自我 LEFT JOIN 取得相鄰對：利用 depth 序號，a.depth = b.depth - 1 連接同一 GUID 的前後代碼（圖5）
建立代碼笛卡爾積矩陣骨架：取所有唯一 Code 值交叉產生 N×N 零矩陣（圖6）
統計實際轉移次數填入矩陣：從相鄰對表 GROUP BY code_from, code_to 計數，UPDATE 至骨架表（圖7）
動態 PIVOT 輸出最終矩陣：同場景一技術，將長表轉寬表（圖8）

-- 步驟1：取得相鄰代碼對
SELECT a.id, a.guid, a.code AS code_from, b.code AS code_to
INTO [TempTable_FromTo]
FROM [TempTable_Depth] a
LEFT JOIN [TempTable_Depth] b ON a.guid = b.guid AND a.[depth] = b.[depth] - 1;

-- 步驟2：建立所有 Code 的笛卡爾積骨架
SELECT DISTINCT code_from, 1 AS num INTO [TempTable_t1] FROM [TempTable_FromTo] ORDER BY code_from;
SELECT a.code_from, b.code_from AS code_to, 0 AS counts
INTO [TempTable_t2]
FROM [TempTable_t1] a CROSS JOIN [TempTable_t1] b;

-- 步驟3：統計實際轉移並更新
SELECT code_from, code_to, COUNT(1) AS counts
INTO [TempTable_t3]
FROM [TempTable_FromTo]
WHERE code_to IS NOT NULL
GROUP BY code_from, code_to;

UPDATE a SET a.counts = b.counts
FROM [TempTable_t2] a, [TempTable_t3] b
WHERE a.code_from = b.code_from AND a.code_to = b.code_to AND b.counts IS NOT NULL;

-- 步驟4：動態 PIVOT 輸出矩陣
DECLARE @s NVARCHAR(MAX) = '';
SELECT @s = @s + ',' + QUOTENAME([code_to]) + '=MAX(CASE WHEN [code_to]=' + QUOTENAME([code_to],'''') + ' THEN counts ELSE NULL END)'
FROM [TempTable_t2] GROUP BY [code_to] ORDER BY [code_to];
EXEC('SELECT [code_from] ' + @s + ' FROM [TempTable_t2] GROUP BY [code_from] ORDER BY [code_from]');