sql:Pivoting and Unpivoting Data in SQL using sql server 2025

 

/* ================================================================
   SQL Server 2025 · 数据透视与逆透视 (Pivoting & Unpivoting) 全场景实战
   场景：国际化珠宝集团（ERP / CRM / HR 三域）
   ----------------------------------------------------------------
   前置：需要本目录下 SQLServer2025_WindowFunctions_JewelryDemo.sql
         先跑过一遍（生成 JewelryAnalytics 库）。
         本脚本会自动检测该库，不存在则给出明确提示并终止。

   覆盖内容
     一、静态 PIVOT（行转列，固定列清单）
     二、动态 PIVOT（列清单来自数据，用于不确定的报表列）
     三、CASE 表达式手写透视（可给每列不同聚合函数）
     四、UNPIVOT（列转行，宽度收敛）
     五、CROSS APPLY + VALUES 逆透视（NULL 不被丢弃）
     六、多层嵌套：透视后再逆透视，宽表还原长表
     七、高并发场景：预聚合表 / 索引 / 隔离级别 / 参数化
     八、性能对比与执行计划要点

   执行方式（sqlcmd 记得带上 o:65001，否则中文乱码）
     sqlcmd -S "localhost\SQL2025" -E -C -b -f "i:65001,o:65001" -i "本文件.sql"
   ================================================================ */

USE [JewelryAnalytics];
GO

-- 前置检查：演示库是否就绪
IF OBJECT_ID(N'erp.SalesOrder', N'U') IS NULL
BEGIN
    THROW 50001, N'未找到 JewelryAnalytics 演示库，请先执行 SQLServer2025_WindowFunctions_JewelryDemo.sql', 1;
END;
GO

/* ----------------------------------------------------------------
   00-A. 开启快照隔离（报表读不阻塞写入的前提）
   ----------------------------------------------------------------
   ALLOW_SNAPSHOT_ISOLATION      启用后可用 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
   READ_COMMITTED_SNAPSHOT       让默认的 READ COMMITTED 走行版本控制，读不拿共享锁
   两者都需要数据库上没有活动连接时才能真正生效，
   所以先切成 SINGLE_USER，改完立刻切回 MULTI_USER。
   ---------------------------------------------------------------- */
IF EXISTS (SELECT 1 FROM sys.databases
           WHERE name = N'JewelryAnalytics'
             AND (snapshot_isolation_state <> 1 OR is_read_committed_snapshot_on <> 1))
BEGIN
    ALTER DATABASE [JewelryAnalytics] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [JewelryAnalytics] SET ALLOW_SNAPSHOT_ISOLATION ON;
    ALTER DATABASE [JewelryAnalytics] SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE [JewelryAnalytics] SET MULTI_USER;
    PRINT N'已开启 ALLOW_SNAPSHOT_ISOLATION + READ_COMMITTED_SNAPSHOT';
END
ELSE
    PRINT N'快照隔离已处于开启状态';
GO

-- 复查隔离级别状态
SELECT
    name                        AS DatabaseName,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'JewelryAnalytics';
GO

SET NOCOUNT ON;
PRINT N'演示库就绪，开始 Pivot / Unpivot 演练';
GO


/* ================================================================
   00. 补齐演示所需的对象
   ----------------------------------------------------------------
   上一轮脚本没有这几张表，这里补：
     hr.EmployeeSalaryMonthly  逐月薪资（宽表待还原用）
     hr.PerformanceReview      绩效评分（透视常用场景）
     erp.SalesTarget           销售目标（对比达成率）
   ================================================================ */
DROP TABLE IF EXISTS hr.EmployeeSalaryMonthly;
DROP TABLE IF EXISTS hr.PerformanceReview;
DROP TABLE IF EXISTS erp.SalesTarget;
DROP TABLE IF EXISTS rpt.MonthlySalesPreAgg;
DROP TABLE IF EXISTS rpt.CategorySalesPreAgg;
GO

IF SCHEMA_ID(N'rpt') IS NULL EXEC (N'CREATE SCHEMA rpt AUTHORIZATION dbo;');
GO

-- 逐月薪资宽表（12 列月份，专门用来演示 UNPIVOT 还原长表）
CREATE TABLE hr.EmployeeSalaryMonthly (
    EmployeeId     INT           NOT NULL,
    PayYear        SMALLINT      NOT NULL,
    CurrencyCode   CHAR(3)       NOT NULL,
    Jan DECIMAL(18,2) NULL, Feb DECIMAL(18,2) NULL, Mar DECIMAL(18,2) NULL,
    Apr DECIMAL(18,2) NULL, May DECIMAL(18,2) NULL, Jun DECIMAL(18,2) NULL,
    Jul DECIMAL(18,2) NULL, Aug DECIMAL(18,2) NULL, Sep DECIMAL(18,2) NULL,
    Oct DECIMAL(18,2) NULL, Nov DECIMAL(18,2) NULL, Dec DECIMAL(18,2) NULL,
    CONSTRAINT PK_EmpSalaryMonthly PRIMARY KEY (EmployeeId, PayYear)
);

-- 绩效评分：员工 × 季度 × 评分维度（长表，天然适合透视）
CREATE TABLE hr.PerformanceReview (
    ReviewId     INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_PerformanceReview PRIMARY KEY,
    EmployeeId   INT          NOT NULL,
    ReviewYear   SMALLINT     NOT NULL,
    ReviewQuarter TINYINT     NOT NULL,     -- 1..4
    MetricName   NVARCHAR(30) NOT NULL,     -- 销售额/客户满意度/陈列规范/团队协作
    Score        DECIMAL(5,2) NOT NULL,     -- 0..100
    CONSTRAINT UQ_Perf UNIQUE (EmployeeId, ReviewYear, ReviewQuarter, MetricName)
);

-- 销售目标：国家/地区 × 品类 × 季度（与实绩对比达成率）
CREATE TABLE erp.SalesTarget (
    CountryCode CHAR(2)      NOT NULL,
    Category    NVARCHAR(20) NOT NULL,
    TargetYear  SMALLINT     NOT NULL,
    TargetQuarter TINYINT    NOT NULL,
    TargetAmount DECIMAL(18,2) NOT NULL,    -- USD
    CONSTRAINT PK_SalesTarget PRIMARY KEY (CountryCode, Category, TargetYear, TargetQuarter)
);
GO

PRINT N'对象补齐完成';
GO


/* ================================================================
   01. 装载新演示数据
   ================================================================ */
SET NOCOUNT ON;

-- ---------- 1.1 逐月薪资宽表（取每位员工现有薪资，按年月轻微波动展开成 12 列） ----------
WITH Latest AS (
    SELECT s.EmployeeId, s.CurrencyCode, s.BaseSalary,
           ROW_NUMBER() OVER (PARTITION BY s.EmployeeId ORDER BY s.EffectiveDate DESC) AS rn
    FROM hr.SalaryHistory s
)
INSERT INTO hr.EmployeeSalaryMonthly
    (EmployeeId, PayYear, CurrencyCode,
     Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
SELECT
    l.EmployeeId, 2025, l.CurrencyCode,
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  1) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  2) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  3) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  4) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  5) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  6) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  7) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  8) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 +  9) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 + 10) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 + 11) AS BIGINT)) % 9) - 4) / 100.0), -2),
    ROUND(l.BaseSalary * (1 + ((ABS(CAST(CHECKSUM(l.EmployeeId * 101 + 12) AS BIGINT)) % 9) - 4) / 100.0), -2)
FROM Latest l
WHERE l.rn = 1;

-- ---------- 1.2 绩效评分（36 人 × 4 季 × 4 维度 = 576 行；部分留空以便观察 NULL 处理） ----------
INSERT INTO hr.PerformanceReview (EmployeeId, ReviewYear, ReviewQuarter, MetricName, Score)
SELECT
    e.EmployeeId,
    2025,
    q.value,
    m.MetricName,
    -- 8% 的评分留空（不插入），模拟"未考核"
    CAST(55 + ((e.EmployeeId * 13 + q.value * 7 + m.MetricId * 11) % 45) AS DECIMAL(5,2))
FROM hr.Employee e
CROSS JOIN GENERATE_SERIES(1, 4) q
CROSS JOIN (VALUES (1, N'销售额'), (2, N'客户满意度'), (3, N'陈列规范'), (4, N'团队协作')) m(MetricId, MetricName)
WHERE (e.EmployeeId * 17 + q.value * 5 + m.MetricId * 3) % 13 <> 0;

-- ---------- 1.3 销售目标（7 国家/地区 × 7 品类 × 4 季度 = 196 行） ----------
INSERT INTO erp.SalesTarget (CountryCode, Category, TargetYear, TargetQuarter, TargetAmount)
SELECT
    c.CountryCode,
    cat.Category,
    2025,
    q.value,
    CAST(30000.0 + ((ABS(CAST(CHECKSUM(c.CountryCode, cat.Category, q.value) AS BIGINT)) % 220000)) AS DECIMAL(18,2))
FROM dbo.Country c
CROSS JOIN (VALUES (N'戒指'),(N'项链'),(N'手镯'),(N'耳饰'),(N'吊坠'),(N'腕表'),(N'其他')) cat(Category)
CROSS JOIN GENERATE_SERIES(1, 4) q;

PRINT N'新演示数据装载完成';
GO


/* ================================================================
   02. 预聚合表（高并发场景的核心手段）
   ----------------------------------------------------------------
   思路：报表不查 6000 行明细，查预先算好的小表。
       行数从 6000 降到 7×24 = 168 行，查询代价下降两个数量级。
   ================================================================ */
DROP TABLE IF EXISTS rpt.MonthlySalesPreAgg;
DROP TABLE IF EXISTS rpt.CategorySalesPreAgg;

-- 国家/地区 × 品类 × 月 的销售预聚合
SELECT
    o.CountryCode,
    p.Category,
    CAST(DATETRUNC(MONTH, o.OrderDate) AS DATE) AS SalesMonth,
    CAST(SUM(o.NetAmountUsd) AS DECIMAL(18,2))      AS SalesUsd,
    COUNT(*)                                        AS OrderCnt,
    CAST(AVG(o.NetAmountUsd) AS DECIMAL(18,2))      AS AvgTicketUsd
INTO rpt.MonthlySalesPreAgg
FROM erp.SalesOrder o
JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
JOIN erp.Product         p ON p.ProductId = l.ProductId
WHERE o.Status IN ('Paid', 'Shipped')
GROUP BY o.CountryCode, p.Category, CAST(DATETRUNC(MONTH, o.OrderDate) AS DATE);

-- 预聚合表必须建索引，否则省下的算力又被扫表吃掉
CREATE UNIQUE CLUSTERED INDEX CX_MonthlySalesPreAgg
    ON rpt.MonthlySalesPreAgg (SalesMonth, CountryCode, Category);
CREATE NONCLUSTERED INDEX IX_MonthlySalesPreAgg_Country
    ON rpt.MonthlySalesPreAgg (CountryCode, SalesMonth)
    INCLUDE (Category, SalesUsd);

-- 国家/地区 × 门店 × 品类 的销售预聚合（看板用）
SELECT
    o.CountryCode,
    o.StoreCode,
    p.Category,
    CAST(SUM(o.NetAmountUsd) AS DECIMAL(18,2)) AS SalesUsd,
    COUNT(*)                                   AS OrderCnt
INTO rpt.CategorySalesPreAgg
FROM erp.SalesOrder o
JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
JOIN erp.Product         p ON p.ProductId = l.ProductId
WHERE o.Status IN ('Paid', 'Shipped')
GROUP BY o.CountryCode, o.StoreCode, p.Category;

CREATE UNIQUE CLUSTERED INDEX CX_CategorySalesPreAgg
    ON rpt.CategorySalesPreAgg (CountryCode, StoreCode, Category);

PRINT N'预聚合表构建完成（rpt.MonthlySalesPreAgg / rpt.CategorySalesPreAgg）';
GO

SELECT N'rpt.MonthlySalesPreAgg'  AS TableName, COUNT(*) AS Rows_ FROM rpt.MonthlySalesPreAgg
UNION ALL SELECT N'rpt.CategorySalesPreAgg', COUNT(*) FROM rpt.CategorySalesPreAgg
UNION ALL SELECT N'hr.EmployeeSalaryMonthly', COUNT(*) FROM hr.EmployeeSalaryMonthly
UNION ALL SELECT N'hr.PerformanceReview',     COUNT(*) FROM hr.PerformanceReview
UNION ALL SELECT N'erp.SalesTarget',          COUNT(*) FROM erp.SalesTarget;
GO


/* ================================================================
   ══════════ 第一部分 · 静态 PIVOT（行转列） ══════════
   ================================================================ */

/* ----------------------------------------------------------------
   P1. 最经典的透视：国家/地区 × 季度 销售额矩阵
   要点：PIVOT 的隐式分组 = 源表中「未出现在聚合和 FOR 里的列」
        所以子查询里只 SELECT 必要的列，多一列就多一层分组
   ---------------------------------------------------------------- */
SELECT
    CountryCode,
    [Q1], [Q2], [Q3], [Q4],
    -- 透视后可直接做行级运算：全年合计
    ISNULL([Q1],0) + ISNULL([Q2],0) + ISNULL([Q3],0) + ISNULL([Q4],0) AS FullYearUsd
FROM (
    SELECT
        o.CountryCode,
        N'Q' + CAST(DATEPART(QUARTER, o.OrderDate) AS NVARCHAR(1)) AS Quarter,
        o.NetAmountUsd
    FROM erp.SalesOrder o
    WHERE o.Status IN ('Paid', 'Shipped')
      AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
) AS src
PIVOT (
    SUM(NetAmountUsd)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt
ORDER BY CountryCode;
GO


/* ----------------------------------------------------------------
   P2. 透视 + 多列聚合的写法对比
   要点：PIVOT 一次只能算一个聚合；
        要同时看「销售额 + 订单数」，标准做法是透视两次再 JOIN
   ---------------------------------------------------------------- */
SELECT
    s.CountryCode,
    s.[Q1] AS Q1_SalesUsd, c.[Q1] AS Q1_OrderCnt,
    s.[Q2] AS Q2_SalesUsd, c.[Q2] AS Q2_OrderCnt,
    s.[Q3] AS Q3_SalesUsd, c.[Q3] AS Q3_OrderCnt,
    s.[Q4] AS Q4_SalesUsd, c.[Q4] AS Q4_OrderCnt
FROM (
    SELECT o.CountryCode,
           N'Q' + CAST(DATEPART(QUARTER, o.OrderDate) AS NVARCHAR(1)) AS Quarter,
           o.NetAmountUsd AS Amt
    FROM erp.SalesOrder o
    WHERE o.Status IN ('Paid','Shipped') AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
) src1
PIVOT (SUM(Amt) FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])) AS s
JOIN (
    SELECT o.CountryCode,
           N'Q' + CAST(DATEPART(QUARTER, o.OrderDate) AS NVARCHAR(1)) AS Quarter,
           1 AS Cnt
    FROM erp.SalesOrder o
    WHERE o.Status IN ('Paid','Shipped') AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
) src2
PIVOT (COUNT(Cnt) FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])) AS c
  ON c.CountryCode = s.CountryCode
ORDER BY s.CountryCode;
GO


/* ----------------------------------------------------------------
   P3. 绩效评分透视：员工 × 评分维度（把长表转成评分卡）
   要点：AVG 配合透视，天然得到"每人每维度平均分"
   ---------------------------------------------------------------- */
SELECT
    e.FullName,
    e.Department,
    p.[销售额], p.[客户满意度], p.[陈列规范], p.[团队协作],
    CAST((ISNULL(p.[销售额],0) + ISNULL(p.[客户满意度],0)
        + ISNULL(p.[陈列规范],0) + ISNULL(p.[团队协作],0)) / 4.0 AS DECIMAL(6,2)) AS AvgScore
FROM (
    SELECT r.EmployeeId, r.MetricName, r.Score
    FROM hr.PerformanceReview r
    WHERE r.ReviewYear = 2025
) src
PIVOT (AVG(Score) FOR MetricName IN ([销售额],[客户满意度],[陈列规范],[团队协作])) AS p
JOIN hr.Employee e ON e.EmployeeId = p.EmployeeId
ORDER BY AvgScore DESC;
GO


/* ----------------------------------------------------------------
   P4. 动态 PIVOT —— ★ 性能与安全的关键区分 ★
   场景：报表的列清单（品类）未来会变，不想每次改 SQL

   这里刻意给两个版本，因为它们的并发安全性天差地别：
     · P4a：QUOTENAME 转义（安全，可放进存储过程）
     · P4b：直接拼接（❌ 有 SQL 注入风险，仅示范，勿用于生产）
   ---------------------------------------------------------------- */
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- P4a ✅ 安全版：列名来自库内数据，仍用 QUOTENAME 转义
SELECT @cols = STRING_AGG(QUOTENAME(Category), N',')
               WITHIN GROUP (ORDER BY Category)
FROM (SELECT DISTINCT Category FROM erp.Product) d;

SET @sql = N'
SELECT CountryCode, ' + @cols + N'
FROM (
    SELECT o.CountryCode, p.Category, o.NetAmountUsd
    FROM erp.SalesOrder o
    JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
    JOIN erp.Product         p ON p.ProductId = l.ProductId
    WHERE o.Status IN (''Paid'',''Shipped'')
      AND o.OrderDate >= ''2025-01-01'' AND o.OrderDate < ''2026-01-01''
) src
PIVOT (SUM(NetAmountUsd) FOR Category IN (' + @cols + N')) AS pvt
ORDER BY CountryCode;';

-- 参数化不是必须的（列名无法参数化），但要加个执行上下文
EXEC sp_executesql @sql;
GO

/* P4b ❌ 危险示范：把用户输入直接拼进 SQL
   攻击者只要把 @Category 传成  [x] FROM t; DROP TABLE hr.Employee; --
   就能拼出可执行的整体语句。演示用，绝不要这样写。 */
DECLARE @CategoryFromUser NVARCHAR(50) = N'戒指';   -- ← 想象这是前端传来的参数

DECLARE @BadSql NVARCHAR(MAX) = N'
SELECT CountryCode, [' + @CategoryFromUser + N'] AS CatSalesUsd
FROM (
    SELECT o.CountryCode, p.Category, o.NetAmountUsd
    FROM erp.SalesOrder o
    JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
    JOIN erp.Product         p ON p.ProductId = l.ProductId
    WHERE o.Status IN (''Paid'',''Shipped'')
) src
PIVOT (SUM(NetAmountUsd) FOR Category IN ([' + @CategoryFromUser + N'])) AS pvt;';

PRINT N'⚠️ 下面这段是「不要这样写」的反面教材，仅打印不执行：';
PRINT @BadSql;
-- EXEC sp_executesql @BadSql;   -- ← 千万别解开这行
GO


/* ================================================================
   ══════════ 第二部分 · CASE 表达式手写透视 ══════════
   ================================================================ */

/* ----------------------------------------------------------------
   C1. CASE 版透视：和 PIVOT 结果一致，但更灵活
   优势：① 每列可用不同聚合函数
        ② 列名可自由起中文/带单位
        ③ 可加窗口函数、可做条件聚合
        ④ 部分场景执行计划更优（无 PIVOT 的额外排序开销）
   ---------------------------------------------------------------- */
SELECT
    o.CountryCode,
    SUM(CASE WHEN DATEPART(QUARTER, o.OrderDate) = 1 THEN o.NetAmountUsd END) AS [第一季度],
    SUM(CASE WHEN DATEPART(QUARTER, o.OrderDate) = 2 THEN o.NetAmountUsd END) AS [第二季度],
    SUM(CASE WHEN DATEPART(QUARTER, o.OrderDate) = 3 THEN o.NetAmountUsd END) AS [第三季度],
    SUM(CASE WHEN DATEPART(QUARTER, o.OrderDate) = 4 THEN o.NetAmountUsd END) AS [第四季度],
    -- 不同聚合函数混用，PIVOT 做不到
    COUNT(CASE WHEN o.NetAmountUsd >= 50000 THEN 1 END)     AS [大单数量],
    MAX(o.NetAmountUsd)                                     AS [最高单笔],
    COUNT(DISTINCT o.CustomerId)                            AS [成交客户数],
    -- 占比也能顺手算出来
    CAST(100.0 * SUM(CASE WHEN DATEPART(QUARTER, o.OrderDate) = 4 THEN o.NetAmountUsd ELSE 0 END)
         / NULLIF(SUM(o.NetAmountUsd), 0) AS DECIMAL(6,2))  AS [Q4占比%]
FROM erp.SalesOrder o
WHERE o.Status IN ('Paid', 'Shipped')
  AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
GROUP BY o.CountryCode
ORDER BY o.CountryCode;
GO


/* ----------------------------------------------------------------
   C2. CASE + 窗口函数：透视的同时算出击同比
   这是 PIVOT 算子做不到的组合（PIVOT 输出后没有源行的排序上下文）
   ---------------------------------------------------------------- */
WITH QuarterMatrix AS (
    SELECT
        o.CountryCode,
        DATEPART(QUARTER, o.OrderDate) AS Q,
        SUM(o.NetAmountUsd)            AS SalesUsd
    FROM erp.SalesOrder o
    WHERE o.Status IN ('Paid', 'Shipped')
    GROUP BY o.CountryCode, DATEPART(QUARTER, o.OrderDate)
)
SELECT DISTINCT
    CountryCode,
    MAX(CASE WHEN Q = 1 THEN SalesUsd END) OVER (PARTITION BY CountryCode) AS Q1,
    MAX(CASE WHEN Q = 2 THEN SalesUsd END) OVER (PARTITION BY CountryCode) AS Q2,
    MAX(CASE WHEN Q = 3 THEN SalesUsd END) OVER (PARTITION BY CountryCode) AS Q3,
    MAX(CASE WHEN Q = 4 THEN SalesUsd END) OVER (PARTITION BY CountryCode) AS Q4,
    -- 用 LAG 算环比，透视结果直接进窗口
    CAST(100.0 * (MAX(CASE WHEN Q = 2 THEN SalesUsd END) OVER (PARTITION BY CountryCode)
                - MAX(CASE WHEN Q = 1 THEN SalesUsd END) OVER (PARTITION BY CountryCode))
         / NULLIF(MAX(CASE WHEN Q = 1 THEN SalesUsd END) OVER (PARTITION BY CountryCode), 0)
         AS DECIMAL(8,2)) AS Q2vsQ1_Pct
FROM QuarterMatrix
ORDER BY CountryCode;
GO


/* ================================================================
   ══════════ 第三部分 · UNPIVOT（列转行） ══════════
   ================================================================ */

/* ----------------------------------------------------------------
   U1. 基础 UNPIVOT：12 个月的薪资宽表 → 长表
   要点：UNPIVOT 的列名（Jan..Dec）会以 nvarchar(128) 出现在结果里
   ---------------------------------------------------------------- */
SELECT
    u.EmployeeId,
    u.CurrencyCode,
    u.PayMonth,
    u.Amount
FROM hr.EmployeeSalaryMonthly
UNPIVOT (
    Amount FOR PayMonth IN
        ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS u
WHERE u.EmployeeId <= 8
ORDER BY u.EmployeeId, u.PayMonth;
GO


/* ----------------------------------------------------------------
   U2. UNPIVOT 会丢弃 NULL —— 这是最容易踩的坑
   要点：某员工 6 月薪资为 NULL 时，UNPIVOT 直接不产出那一行
        如果报表要求"补齐 12 个月"，必须改走 CROSS APPLY（见 U4）
   ---------------------------------------------------------------- */
UPDATE hr.EmployeeSalaryMonthly SET Jun = NULL WHERE EmployeeId = 3;

SELECT N'UNPIVOT 后行数（EmployeeId=3）' AS Mode, COUNT(*) AS Rows_ FROM (
    SELECT EmployeeId, PayMonth, Amount
    FROM hr.EmployeeSalaryMonthly
    UNPIVOT (Amount FOR PayMonth IN
        ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) u
    WHERE u.EmployeeId = 3
) x
UNION ALL
SELECT N'源宽表列数（应为 13 = 12月 + 2键列）', 13;
GO


/* ----------------------------------------------------------------
   U3. UNPIVOT 后接窗口函数：宽表直接变月度趋势
   ---------------------------------------------------------------- */
SELECT
    u.EmployeeId,
    e.FullName,
    u.PayMonth,
    u.Amount,
    -- 12 个月累计
    SUM(u.Amount) OVER (PARTITION BY u.EmployeeId
                        ORDER BY u.PayMonth ROWS UNBOUNDED PRECEDING) AS YtdPaid,
    -- 与上月差
    u.Amount - LAG(u.Amount) OVER (PARTITION BY u.EmployeeId ORDER BY u.PayMonth) AS MoMChange,
    -- 与 12 个月均值比，看浮动是否异常
    CAST(u.Amount - AVG(u.Amount) OVER (PARTITION BY u.EmployeeId) AS DECIMAL(18,2)) AS DiffVsPersonalAvg
FROM hr.EmployeeSalaryMonthly
UNPIVOT (Amount FOR PayMonth IN
    ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS u
JOIN hr.Employee e ON e.EmployeeId = u.EmployeeId
ORDER BY u.EmployeeId, u.PayMonth;
GO


/* ----------------------------------------------------------------
   U4. ★ CROSS APPLY + VALUES 逆透视：UNPIVOT 的全面升级版 ★
   优势：① 保留 NULL
        ② 可同时输出多个度量（月份、金额、币种、是否发放）
        ③ 每个字段可单独推导，不受列类型一致约束
        ④ 数据量不大时，写法比 UNPIVOT 直观得多
   ---------------------------------------------------------------- */
SELECT
    s.EmployeeId,
    s.CurrencyCode,
    v.PayMonth,
    v.MonthNo,
    v.Amount,
    CASE WHEN v.Amount IS NULL THEN N'未发放' ELSE N'已发放' END AS PayStatus
FROM hr.EmployeeSalaryMonthly s
CROSS APPLY (VALUES
    (N'Jan',  1, s.Jan), (N'Feb',  2, s.Feb), (N'Mar',  3, s.Mar),
    (N'Apr',  4, s.Apr), (N'May',  5, s.May), (N'Jun',  6, s.Jun),
    (N'Jul',  7, s.Jul), (N'Aug',  8, s.Aug), (N'Sep',  9, s.Sep),
    (N'Oct', 10, s.Oct), (N'Nov', 11, s.Nov), (N'Dec', 12, s.Dec)
) AS v(PayMonth, MonthNo, Amount)
WHERE s.EmployeeId = 3          -- + EmployeeId=3 的 Jun 是 NULL，可对比 U2 的结果
ORDER BY v.MonthNo;
GO


/* ----------------------------------------------------------------
   U5. UNPIVOT 用于「多列同质指标」的横向对比
   场景：绩效评分的 4 个维度如果想算出「本人最高分维度」，
        宽表格式做不了，转长表后一个窗口函数就搞定
   ---------------------------------------------------------------- */
WITH ScoreWide AS (
    -- 第一层：长表 → 宽表（评分卡）
    SELECT EmployeeId, [销售额], [客户满意度], [陈列规范], [团队协作]
    FROM (
        SELECT EmployeeId, MetricName, Score
        FROM hr.PerformanceReview
        WHERE ReviewYear = 2025
    ) AS rs
    PIVOT (AVG(Score) FOR MetricName
           IN ([销售额],[客户满意度],[陈列规范],[团队协作])) AS p
),
ScoreLong AS (
    -- 第二层：宽表 → 长表。★ 不能在同一层 PIVOT 后又 UNPIVOT 同一批列，
    --          PIVOT 的输出已重命名，必须用 CTE 隔开一层
    SELECT EmployeeId, MetricName, Score
    FROM ScoreWide
    UNPIVOT (Score FOR MetricName IN ([销售额],[客户满意度],[陈列规范],[团队协作])) AS u
)
SELECT
    e.FullName,
    sl.MetricName,
    sl.Score,
    RANK() OVER (PARTITION BY sl.EmployeeId ORDER BY sl.Score DESC) AS RankInSelf,
    AVG(sl.Score) OVER (PARTITION BY sl.EmployeeId)                 AS PersonalAvg,
    sl.Score - AVG(sl.Score) OVER (PARTITION BY sl.EmployeeId)      AS DiffVsSelf
FROM ScoreLong sl
JOIN hr.Employee e ON e.EmployeeId = sl.EmployeeId
WHERE e.Department = N'销售'
ORDER BY e.FullName, RankInSelf;
GO


/* ================================================================
   ══════════ 第四部分 · 嵌套与往返（Round-trip） ══════════
   ================================================================ */

/* ----------------------------------------------------------------
   N1. 透视 → 逆透视往返，验证可逆性
   要点：① UNPIVOT 会把被转置掉的列从输出里移除（实测确认），
            输出只剩「非转置列 + 名称列 + 值列」，
            所以要对照原宽表值必须 JOIN 回源表，不能直接写 u.[戒指]
        ② PIVOT 会把缺失组合补成 NULL（不是 0），
           回来时 UNPIVOT 又把 NULL 丢掉 → 行数可能少于 分组数 × 列数
   ---------------------------------------------------------------- */
DROP TABLE IF EXISTS #CatMatrix;

SELECT CountryCode, [戒指], [项链], [手镯], [耳饰]
INTO #CatMatrix
FROM (
    SELECT o.CountryCode, p.Category, SUM(o.NetAmountUsd) AS Amt
    FROM erp.SalesOrder o
    JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
    JOIN erp.Product         p ON p.ProductId = l.ProductId
    WHERE o.Status IN ('Paid','Shipped')
      AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.CountryCode, p.Category
) src
PIVOT (SUM(Amt) FOR Category IN ([戒指],[项链],[手镯],[耳饰])) AS pvt;

SELECT N'宽表行数' AS StepName, COUNT(*) AS Rows_ FROM #CatMatrix
UNION ALL
SELECT N'宽表非空单元格数（= UNPIVOT 后应有的行数）',
       (SELECT SUM(CASE WHEN x IS NULL THEN 0 ELSE 1 END)
        FROM (SELECT [戒指] AS x FROM #CatMatrix
              UNION ALL SELECT [项链] FROM #CatMatrix
              UNION ALL SELECT [手镯] FROM #CatMatrix
              UNION ALL SELECT [耳饰] FROM #CatMatrix) t);

-- ★ 关键结论（实测确认）：
--   UNPIVOT 的输出列只有「非转置列 + 两个新列（名称列、值列）」，
--   被转置的原列（戒指/项链/手镯/耳饰）在输出中【已不存在】，
--   所以 u.[戒指] 这类写法必然报「列名无效」。
--   要拿原宽表值，只能回到源表用 CASE 按名称列分支取值。
--   下面用 CTE 把宽表列原样带进来，再与逆透视结果 JOIN，即可完成往返自检。
WITH Long AS (
    SELECT u.CountryCode, u.Category, u.Amt
    FROM #CatMatrix
    UNPIVOT (Amt FOR Category IN ([戒指],[项链],[手镯],[耳饰])) AS u
)
SELECT
    l.CountryCode,
    l.Category,
    l.Amt                                   AS LongFormValue,
    CASE l.Category                          -- 从源宽表按名称取回原值
        WHEN N'戒指' THEN m.[戒指] WHEN N'项链' THEN m.[项链]
        WHEN N'手镯' THEN m.[手镯] WHEN N'耳饰' THEN m.[耳饰]
    END                                     AS WideColValue,
    CASE WHEN l.Amt = CASE l.Category
                          WHEN N'戒指' THEN m.[戒指] WHEN N'项链' THEN m.[项链]
                          WHEN N'手镯' THEN m.[手镯] WHEN N'耳饰' THEN m.[耳饰]
                      END
         THEN N'OK' ELSE N'MISMATCH' END    AS RoundTripCheck
FROM Long l
JOIN #CatMatrix m ON m.CountryCode = l.CountryCode
ORDER BY l.CountryCode, l.Category;
GO
-- ★ 结论：UNPIVOT 的输出里，被转置的列会以 nvarchar 列名出现，
--   同时原宽表列在输出中已消失（这正是"逆透视"的含义）。
--   所以 N1 只能按 Category 分支去取原值做自检，无法直接 SELECT m.[戒指]。
SELECT TOP 8
    u.CountryCode,
    u.Category,
    u.Amt          AS LongFormValue
FROM #CatMatrix
UNPIVOT (Amt FOR Category IN ([戒指],[项链],[手镯],[耳饰])) AS u
ORDER BY u.CountryCode, u.Category;
GO


/* ----------------------------------------------------------------
   N2. 二次透视：把「国家×季度」结果再按地区汇总
   要点：嵌套 PIVOT 不能写在一个表达式里，
        必须用 CTE 逐层派生：先算 (Region, 季度) 的聚合，再对季度列透视
   ---------------------------------------------------------------- */
WITH Base AS (
    SELECT
        o.CountryCode,
        c.Region,
        CAST(DATEPART(QUARTER, o.OrderDate) AS NVARCHAR(1)) AS Q,
        o.NetAmountUsd
    FROM erp.SalesOrder o
    JOIN dbo.Country c ON c.CountryCode = o.CountryCode
    WHERE o.Status IN ('Paid','Shipped')
      AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
),
RegionQuarter AS (
    SELECT Region, N'Q' + Q AS QuarterLabel, SUM(NetAmountUsd) AS Amt
    FROM Base
    GROUP BY Region, N'Q' + Q
)
SELECT
    Region,
    [Q1] AS Q1销售额, [Q2] AS Q2销售额,
    [Q3] AS Q3销售额, [Q4] AS Q4销售额,
    CAST(ISNULL([Q1],0) + ISNULL([Q2],0) + ISNULL([Q3],0) + ISNULL([Q4],0) AS DECIMAL(18,2)) AS 全年合计
FROM RegionQuarter
PIVOT (SUM(Amt) FOR QuarterLabel IN ([Q1],[Q2],[Q3],[Q4])) AS pvt
ORDER BY 全年合计 DESC;
GO


/* ================================================================
   ══════════ 第五部分 · 高并发场景实践 ══════════
   ================================================================ */

/* ----------------------------------------------------------------
   H1. 为什么不能直接对明细透视（把 3 个代价讲清楚）
     ① 6000 行明细要全扫：STATISTICS IO 的 logical reads 会很直观
     ② PIVOT 前必须按分组键排序：执行计划里多一个 Sort
     ③ 每次刷新页面都重算一遍：并发 1000 时 CPU 直接打满
   ---------------------------------------------------------------- */
SET STATISTICS IO, TIME ON;

PRINT N'--- 反例：直接对明细透视 ---';
SELECT CountryCode, [戒指], [项链], [手镯], [耳饰], [吊坠], [腕表], [其他]
FROM (
    SELECT o.CountryCode, p.Category, o.NetAmountUsd
    FROM erp.SalesOrder o
    JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
    JOIN erp.Product         p ON p.ProductId = l.ProductId
    WHERE o.Status IN ('Paid','Shipped') AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
) src
PIVOT (SUM(NetAmountUsd) FOR Category IN ([戒指],[项链],[手镯],[耳饰],[吊坠],[腕表],[其他])) AS pvt
ORDER BY CountryCode;

PRINT N'--- 正例：查预聚合表（行数从 11296 降到 168） ---';
SELECT CountryCode, [戒指], [项链], [手镯], [耳饰], [吊坠], [腕表], [其他]
FROM (
    SELECT CountryCode, Category, SalesUsd
    FROM rpt.MonthlySalesPreAgg
    WHERE SalesMonth >= '2025-01-01' AND SalesMonth < '2026-01-01'
) src
PIVOT (SUM(SalesUsd) FOR Category IN ([戒指],[项链],[手镯],[耳饰],[吊坠],[腕表],[其他])) AS pvt
ORDER BY CountryCode;

SET STATISTICS IO, TIME OFF;
GO


/* ----------------------------------------------------------------
   H2. 参数化查询：让执行计划可复用（高并发的头号要点）
   要点：sp_executesql + 强类型参数 → 计划缓存命中率 100%
        对比「拼接字符串」→ 每个不同取值都是一份新计划，缓存爆炸
   ---------------------------------------------------------------- */
-- ✅ 参数化：无论换哪个国家/地区、哪个季度，都是同一个计划
DECLARE @Country CHAR(2) = 'CN', @Year SMALLINT = 2025, @Quarter TINYINT = 4;

DECLARE @P1 NVARCHAR(MAX) = N'
SELECT
    p.Category,
    CAST(SUM(p.SalesUsd) AS DECIMAL(18,2)) AS SalesUsd,
    SUM(p.OrderCnt)                        AS OrderCnt,
    CAST(AVG(p.AvgTicketUsd) AS DECIMAL(18,2)) AS AvgTicketUsd
FROM rpt.MonthlySalesPreAgg p
WHERE p.CountryCode = @CountryCode
  AND p.SalesMonth >= DATEFROMPARTS(@Yr, (@Qtr - 1) * 3 + 1, 1)
  AND p.SalesMonth <  DATEADD(QUARTER, 1, DATEFROMPARTS(@Yr, (@Qtr - 1) * 3 + 1, 1))
GROUP BY p.Category
ORDER BY SalesUsd DESC;';

PRINT N'--- 参数化执行 ---';
EXEC sp_executesql @P1,
     N'@CountryCode CHAR(2), @Yr SMALLINT, @Qtr TINYINT',
     @CountryCode = @Country, @Yr = @Year, @Qtr = @Quarter;
GO

-- ❌ 反例：同样的查询，把条件拼进 SQL 字符串 → 计划缓存被撑爆
DECLARE @Bad NVARCHAR(MAX) =
    N'SELECT CountryCode, SUM(SalesUsd) FROM rpt.MonthlySalesPreAgg WHERE CountryCode = ''CN'' GROUP BY CountryCode;';
PRINT N'⛔ 不推荐（每个不同字面量都会产生一份独立计划，除零风险与缓存膨胀）：';
PRINT @Bad;
GO


/* ----------------------------------------------------------------
   H3. 读写分离与快照隔离：报表与分析查询互不阻塞
   要点：本脚本开头（00-A 节）已开启
        ALLOW_SNAPSHOT_ISOLATION + READ_COMMITTED_SNAPSHOT。
        此时：
          · READ_COMMITTED 默认走行版本控制，读不拿共享锁
            → 夜批写预聚合表时，报表查询不会被写事务堵住
          · 显式 SNAPSHOT 隔离则保证整条查询看到同一个一致性快照
            → 跨多张表做报表时不会出现"读到一半数据变了"
   ---------------------------------------------------------------- */
-- 报表会话：显式声明快照隔离，整条查询读同一个时间点
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SELECT TOP 5
    CountryCode, Category, SalesMonth, SalesUsd
FROM rpt.MonthlySalesPreAgg
WHERE SalesMonth >= '2025-10-01'
ORDER BY SalesUsd DESC;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

-- 用 READPAST 跳过被夜批锁住的行，保证报表永远不排队
SELECT TOP 5
    CountryCode, Category, SalesMonth, SalesUsd
FROM rpt.MonthlySalesPreAgg WITH (READPAST)
WHERE SalesMonth >= '2025-10-01'
ORDER BY SalesUsd DESC;
GO


/* ----------------------------------------------------------------
   H4. 预聚合表的高并发刷新：不锁表、可秒级切换
   思路：影子表 → 全量重建 → 原子改名（元数据操作，毫秒级）
        读方在切换瞬间要么看到旧表、要么看到新表，永远不会读到半个表
   ---------------------------------------------------------------- */
DROP TABLE IF EXISTS rpt.MonthlySalesPreAgg_Shadow;

SELECT
    o.CountryCode, p.Category,
    CAST(DATETRUNC(MONTH, o.OrderDate) AS DATE) AS SalesMonth,
    CAST(SUM(o.NetAmountUsd) AS DECIMAL(18,2))      AS SalesUsd,
    COUNT(*)                                        AS OrderCnt,
    CAST(AVG(o.NetAmountUsd) AS DECIMAL(18,2))      AS AvgTicketUsd
INTO rpt.MonthlySalesPreAgg_Shadow
FROM erp.SalesOrder o
JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
JOIN erp.Product         p ON p.ProductId = l.ProductId
WHERE o.Status IN ('Paid','Shipped')
GROUP BY o.CountryCode, p.Category, CAST(DATETRUNC(MONTH, o.OrderDate) AS DATE);

CREATE UNIQUE CLUSTERED INDEX CX_MonthlySalesPreAgg_Shadow
    ON rpt.MonthlySalesPreAgg_Shadow (SalesMonth, CountryCode, Category);

-- 原子切换：sp_rename 只在元数据上加短锁，不阻塞读
EXEC sp_rename N'rpt.MonthlySalesPreAgg',      N'MonthlySalesPreAgg_Old',    N'OBJECT';
EXEC sp_rename N'rpt.MonthlySalesPreAgg_Shadow', N'MonthlySalesPreAgg',      N'OBJECT';
DROP TABLE IF EXISTS rpt.MonthlySalesPreAgg_Old;

PRINT N'预聚合表已原子切换完成（读方全程无阻塞）';
GO

-- 验证切换后仍可正常查询
SELECT TOP 5 CountryCode, Category, SalesMonth, SalesUsd
FROM rpt.MonthlySalesPreAgg ORDER BY SalesUsd DESC;
GO


/* ----------------------------------------------------------------
   H5. 大批量透视结果的落地：SELECT INTO 比 INSERT ... SELECT 更省日志
   要点：SELECT INTO 走最小日志记录（在简单恢复模式或批量操作下），
        写入 10 万行透视结果时比 INSERT 快一截
   ---------------------------------------------------------------- */
DROP TABLE IF EXISTS rpt.CountryCategoryMatrix;

SELECT
    CountryCode, [戒指], [项链], [手镯], [耳饰], [吊坠], [腕表], [其他]
INTO rpt.CountryCategoryMatrix
FROM (
    SELECT CountryCode, Category, SalesUsd
    FROM rpt.MonthlySalesPreAgg
    WHERE SalesMonth >= '2025-01-01' AND SalesMonth < '2026-01-01'
) src
PIVOT (SUM(SalesUsd) FOR Category IN ([戒指],[项链],[手镯],[耳饰],[吊坠],[腕表],[其他])) AS pvt;

-- 矩阵宽表也要有主键，不然下次 JOIN 又要扫全表
ALTER TABLE rpt.CountryCategoryMatrix
    ADD CONSTRAINT PK_CountryCategoryMatrix PRIMARY KEY (CountryCode);

SELECT * FROM rpt.CountryCategoryMatrix ORDER BY CountryCode;
GO


/* ================================================================
   ══════════ 第六部分 · 实战报表 ══════════
   ================================================================ */

/* ----------------------------------------------------------------
   R-A. 销售看板：门店 × 品类矩阵（管理层最常要的那张表）
   ---------------------------------------------------------------- */
SELECT
    StoreCode,
    ISNULL([戒指],0) AS 戒指, ISNULL([项链],0) AS 项链,
    ISNULL([手镯],0) AS 手镯, ISNULL([耳饰],0) AS 耳饰,
    ISNULL([吊坠],0) AS 吊坠, ISNULL([腕表],0) AS 腕表,
    ISNULL([其他],0) AS 其他,
    ISNULL([戒指],0)+ISNULL([项链],0)+ISNULL([手镯],0)+ISNULL([耳饰],0)
  + ISNULL([吊坠],0)+ISNULL([腕表],0)+ISNULL([其他],0) AS 合计
FROM (
    SELECT StoreCode, Category, SalesUsd FROM rpt.CategorySalesPreAgg
) src
PIVOT (SUM(SalesUsd) FOR Category IN ([戒指],[项链],[手镯],[耳饰],[吊坠],[腕表],[其他])) AS pvt
ORDER BY 合计 DESC;
GO


/* ----------------------------------------------------------------
   R-B. 目标达成率：销售目标长表 vs 实绩长表，先归一化再对比
   要点：两边维度必须对齐（季度要统一成整数），否则 JOIN 出笛卡尔积
   ---------------------------------------------------------------- */
WITH Actual AS (
    SELECT
        o.CountryCode, p.Category,
        CAST(DATEPART(QUARTER, o.OrderDate) AS TINYINT) AS Qtr,
        SUM(o.NetAmountUsd) AS ActualUsd
    FROM erp.SalesOrder o
    JOIN erp.SalesOrderLine l ON l.OrderId = o.OrderId
    JOIN erp.Product         p ON p.ProductId = l.ProductId
    WHERE o.Status IN ('Paid','Shipped')
      AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.CountryCode, p.Category, CAST(DATEPART(QUARTER, o.OrderDate) AS TINYINT)
),
Joined AS (
    SELECT
        t.CountryCode, t.Category, t.TargetQuarter,
        CAST(t.TargetAmount AS DECIMAL(18,2))         AS TargetUsd,
        CAST(ISNULL(a.ActualUsd, 0) AS DECIMAL(18,2)) AS ActualUsd
    FROM erp.SalesTarget t
    LEFT JOIN Actual a
           ON a.CountryCode = t.CountryCode
          AND a.Category    = t.Category
          AND a.Qtr         = t.TargetQuarter
    WHERE t.TargetYear = 2025
),
-- ★ 关键：把「目标/实绩」编码进一个整数列，必须先在子查询里算成列，
--   PIVOT 的 IN 清单只接受「列名或字面量」，
--   写 [1 + 100] 会被当成列名而不是表达式 → 报「列名 '1 + 100' 无效」
Coded AS (
    SELECT CountryCode, CAST(TargetQuarter AS INT) AS QKey, TargetUsd AS Amt
    FROM Joined
    UNION ALL
    SELECT CountryCode, CAST(TargetQuarter AS INT) + 100, ActualUsd
    FROM Joined
)
SELECT
    CountryCode,
    ISNULL([1],0) AS Q1目标, ISNULL([2],0) AS Q2目标,
    ISNULL([3],0) AS Q3目标, ISNULL([4],0) AS Q4目标,
    ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)             AS 全年目标,
    ISNULL([101],0) AS Q1实绩, ISNULL([102],0) AS Q2实绩,
    ISNULL([103],0) AS Q3实绩, ISNULL([104],0) AS Q4实绩,
    ISNULL([101],0)+ISNULL([102],0)+ISNULL([103],0)+ISNULL([104],0)     AS 全年实绩,
    -- 达成率：实绩 / 目标
    CAST(100.0 * (ISNULL([101],0)+ISNULL([102],0)+ISNULL([103],0)+ISNULL([104],0))
         / NULLIF(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0), 0)
         AS DECIMAL(6,2))                                                AS 全年达成率Pct
FROM Coded
PIVOT (SUM(Amt) FOR QKey IN ([1],[2],[3],[4],[101],[102],[103],[104])) AS pvt
ORDER BY 全年达成率Pct DESC;
GO


/* ----------------------------------------------------------------
   R-C. HR 薪酬看板：部门 × 职级 人数与人效矩阵
   ---------------------------------------------------------------- */
SELECT
    Department,
    ISNULL([3],0) AS L3, ISNULL([4],0) AS L4, ISNULL([5],0) AS L5,
    ISNULL([6],0) AS L6, ISNULL([7],0) AS L7, ISNULL([9],0) AS L9,
    ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)
  + ISNULL([6],0)+ISNULL([7],0)+ISNULL([9],0) AS HeadCount
FROM (
    SELECT Department, JobLevel FROM hr.Employee
) src
PIVOT (COUNT(JobLevel) FOR JobLevel IN ([3],[4],[5],[6],[7],[9])) AS pvt
ORDER BY HeadCount DESC;
GO


/* ----------------------------------------------------------------
   R-D. CRM 渠道分析：客户等级 × 互动渠道矩阵
   ---------------------------------------------------------------- */
SELECT
    Tier,
    ISNULL([Store],0)  AS 门店, ISNULL([Web],0)   AS 官网,
    ISNULL([App],0)    AS APP,  ISNULL([Phone],0) AS 电话,
    ISNULL([WeChat],0) AS 微信,
    ISNULL([Store],0)+ISNULL([Web],0)+ISNULL([App],0)
  + ISNULL([Phone],0)+ISNULL([WeChat],0) AS 互动总次数
FROM (
    SELECT c.Tier, i.Channel, 1 AS One
    FROM crm.Interaction i
    JOIN crm.Customer    c ON c.CustomerId = i.CustomerId
) src
PIVOT (COUNT(One) FOR Channel IN ([Store],[Web],[App],[Phone],[WeChat])) AS pvt
ORDER BY 互动总次数 DESC;
GO


/* ----------------------------------------------------------------
   R-E. 长表标准报表格式：宽表给管理层看，长表给 BI 工具吃
   用 UNPIVOT 把 R-A 的宽表拆回长表，导出 CSV 给 Power BI
   要点：UNPIVOT 之后不能用源表别名做前缀（原列已被移除），
        只能用输出列名
   ---------------------------------------------------------------- */
SELECT
    u.CountryCode,
    u.Category,
    u.SalesUsd
FROM rpt.CountryCategoryMatrix
UNPIVOT (SalesUsd FOR Category IN
    ([戒指],[项链],[手镯],[耳饰],[吊坠],[腕表],[其他])) AS u
WHERE u.SalesUsd IS NOT NULL
ORDER BY u.CountryCode, u.Category;
GO


/* ================================================================
   07. 清理临时对象（保留可复用的预聚合表）
   ================================================================ */
-- 如需彻底清理，解开下面的注释
-- DROP TABLE IF EXISTS rpt.CountryCategoryMatrix;
-- DROP TABLE IF EXISTS rpt.MonthlySalesPreAgg;
-- DROP TABLE IF EXISTS rpt.CategorySalesPreAgg;
GO

PRINT N'全部 Pivot / Unpivot 演示执行完毕。';
GO