SQL Server多行转列查询优化：替代低效多表关联方案

我是SQL新手，当前使用SQL Server数据库，现有Measurement2表（含testPointKey、dataType、dataValue等字段），需要根据多个dataType值查询数据，并将结果转为单行多列格式。

表结构 #

CREATE TABLE TestPoint2 (
    recordKey           INTEGER     NOT NULL PRIMARY KEY,
    testSessionKey      INTEGER     NOT NULL REFERENCES TestSession2(recordKey) ON DELETE CASCADE,
    voltage             FLOAT,
    amps                FLOAT,
    phase               FLOAT,
    seconds             INTEGER,
    iterations          INTEGER,
);

CREATE TABLE Measurement2 (
    id                  INTEGER     NOT NULL PRIMARY KEY,
    testPointKey        INTEGER     NOT NULL REFERENCES TestPoint2(recordKey) ON DELETE CASCADE,
    mIndex              INTEGER,
    dataType            TEXT,
    dataValue           FLOAT,
);

期望输出格式 #

当前方案问题 #

目前采用多次自连接的方式实现（如下SQL），虽能得到正确结果，但查询速度极慢，且dataType类型越多性能越差，寻求更优方案：

SELECT t1.dataValue AS hp, t2.dataValue AS tempHp, t3.dataValue AS keithley, t4.dataValue AS tempKeithley, t5.dataValue AS va, t6.dataValue AS vc, t7.dataValue AS vavc, t8.dataValue AS ia, t9.dataValue AS ic, t10.dataValue AS phaseVc, t11.dataValue AS phaseIa, t12.dataValue AS phaseIc, t13.dataValue AS tempVaa, t14.dataValue AS tempVb, t15.dataValue AS tempVc, t16.dataValue AS tempIa, t17.dataValue AS tempIb, t18.dataValue AS tempIc, t19.dataValue AS tempCta, t20.dataValue AS tempCtb, t21.dataValue AS tempCtc, t22.dataValue AS tempSom
FROM Measurement2 t1 
INNER JOIN Measurement2 t2 ON t1.testPointKey = t2.testPointKey 
INNER JOIN Measurement2 t3 ON t1.testPointKey = t3.testPointKey 
INNER JOIN Measurement2 t4 ON t1.testPointKey = t4.testPointKey 
INNER JOIN Measurement2 t5 ON t1.testPointKey = t5.testPointKey 
INNER JOIN Measurement2 t6 ON t1.testPointKey = t6.testPointKey 
INNER JOIN Measurement2 t7 ON t1.testPointKey = t7.testPointKey 
INNER JOIN Measurement2 t8 ON t1.testPointKey = t8.testPointKey 
INNER JOIN Measurement2 t9 ON t1.testPointKey = t9.testPointKey 
INNER JOIN Measurement2 t10 ON t1.testPointKey = t10.testPointKey 
INNER JOIN Measurement2 t11 ON t1.testPointKey = t11.testPointKey 
INNER JOIN Measurement2 t12 ON t1.testPointKey = t12.testPointKey 
INNER JOIN Measurement2 t13 ON t1.testPointKey = t13.testPointKey 
INNER JOIN Measurement2 t14 ON t1.testPointKey = t14.testPointKey 
INNER JOIN Measurement2 t15 ON t1.testPointKey = t15.testPointKey 
INNER JOIN Measurement2 t16 ON t1.testPointKey = t16.testPointKey 
INNER JOIN Measurement2 t17 ON t1.testPointKey = t17.testPointKey 
INNER JOIN Measurement2 t18 ON t1.testPointKey = t18.testPointKey 
INNER JOIN Measurement2 t19 ON t1.testPointKey = t19.testPointKey 
INNER JOIN Measurement2 t20 ON t1.testPointKey = t20.testPointKey 
INNER JOIN Measurement2 t21 ON t1.testPointKey = t21.testPointKey 
INNER JOIN Measurement2 t22 ON t1.testPointKey = t22.testPointKey 
INNER JOIN TestPoint2 tp1 ON tp1.recordKey = t22.testPointKey
WHERE CAST(t1.dataType as VARCHAR) = 'hp' 
  AND CAST(t2.dataType as VARCHAR) = 'tempHp'
  AND CAST(t3.dataType as VARCHAR) = 'keithley' 
  AND CAST(t4.dataType as VARCHAR) = 'tempKeithley' 
  AND CAST(t5.dataType as VARCHAR) = 'va' 
  AND CAST(t6.dataType as VARCHAR) = 'vc' 
  AND CAST(t7.dataType as VARCHAR) = 'vavc' 
  AND CAST(t8.dataType as VARCHAR) = 'ia' 
  AND CAST(t9.dataType as VARCHAR) = 'ic'
  AND CAST(t10.dataType as VARCHAR) = 'phaseVc'
  AND CAST(t11.dataType as VARCHAR) = 'phaseIa'
  AND CAST(t12.dataType as VARCHAR) = 'phaseIc'
  AND CAST(t13.dataType as VARCHAR) = 'tempVaa'
  AND CAST(t14.dataType as VARCHAR) = 'tempVb'
  AND CAST(t15.dataType as VARCHAR) = 'tempVc'
  AND CAST(t16.dataType as VARCHAR) = 'tempIa'
  AND CAST(t17.dataType as VARCHAR) = 'tempIb'
  AND CAST(t18.dataType as VARCHAR) = 'tempIc'
  AND CAST(t19.dataType as VARCHAR) = 'tempCta'
  AND CAST(t20.dataType as VARCHAR) = 'tempCtb'
  AND CAST(t21.dataType as VARCHAR) = 'tempCtc'
  AND CAST(t22.dataType as VARCHAR) = 'tempSom';

优化方案 #

1. 使用PIVOT操作符（推荐） #

SQL Server原生支持PIVOT进行行转列，只需扫描一次表即可完成转换，性能远高于多次自连接：

SELECT 
    hp, tempHp, keithley, tempKeithley, va, vc, vavc, ia, ic,
    phaseVc, phaseIa, phaseIc, tempVaa, tempVb, tempVc,
    tempIa, tempIb, tempIc, tempCta, tempCtb, tempCtc, tempSom
FROM (
    SELECT 
        testPointKey,
        CAST(dataType AS VARCHAR(50)) AS dataType, -- 转换类型避免TEXT的性能问题
        dataValue
    FROM Measurement2
) AS src
PIVOT (
    MAX(dataValue) -- 每个testPointKey对应唯一dataType，用MAX/SUM均可
    FOR dataType IN (
        hp, tempHp, keithley, tempKeithley, va, vc, vavc, ia, ic,
        phaseVc, phaseIa, phaseIc, tempVaa, tempVb, tempVc,
        tempIa, tempIb, tempIc, tempCta, tempCtb, tempCtc, tempSom
    )
) AS pvt
INNER JOIN TestPoint2 tp ON tp.recordKey = pvt.testPointKey;

2. 条件聚合（兼容性好） #

如果对PIVOT不熟悉，也可以用CASE WHEN配合聚合函数实现，性能同样出色：

SELECT 
    tp.recordKey,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'hp' THEN m.dataValue END) AS hp,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempHp' THEN m.dataValue END) AS tempHp,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'keithley' THEN m.dataValue END) AS keithley,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempKeithley' THEN m.dataValue END) AS tempKeithley,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'va' THEN m.dataValue END) AS va,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'vc' THEN m.dataValue END) AS vc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'vavc' THEN m.dataValue END) AS vavc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'ia' THEN m.dataValue END) AS ia,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'ic' THEN m.dataValue END) AS ic,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'phaseVc' THEN m.dataValue END) AS phaseVc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'phaseIa' THEN m.dataValue END) AS phaseIa,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'phaseIc' THEN m.dataValue END) AS phaseIc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempVaa' THEN m.dataValue END) AS tempVaa,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempVb' THEN m.dataValue END) AS tempVb,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempVc' THEN m.dataValue END) AS tempVc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempIa' THEN m.dataValue END) AS tempIa,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempIb' THEN m.dataValue END) AS tempIb,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempIc' THEN m.dataValue END) AS tempIc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempCta' THEN m.dataValue END) AS tempCta,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempCtb' THEN m.dataValue END) AS tempCtb,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempCtc' THEN m.dataValue END) AS tempCtc,
    MAX(CASE WHEN CAST(m.dataType AS VARCHAR(50)) = 'tempSom' THEN m.dataValue END) AS tempSom
FROM Measurement2 m
INNER JOIN TestPoint2 tp ON tp.recordKey = m.testPointKey
GROUP BY tp.recordKey;

3. 索引优化（关键） #

不管用哪种方案，都需要给Measurement2表添加合适的索引来加速查询：

-- 创建联合索引，包含需要的字段，避免键查找
CREATE NONCLUSTERED INDEX IX_Measurement2_TestPointKey_DataType
ON Measurement2 (testPointKey, CAST(dataType AS VARCHAR(50)))
INCLUDE (dataValue);

-- 更优方案：如果能修改dataType字段类型为VARCHAR(50)，索引可简化为：
-- CREATE NONCLUSTERED INDEX IX_Measurement2_TestPointKey_DataType
-- ON Measurement2 (testPointKey, dataType)
-- INCLUDE (dataValue);

说明：dataType使用TEXT类型会导致CAST操作无法利用索引，建议改成VARCHAR类型（SQL Server中TEXT是过时类型，推荐用VARCHAR(MAX)或合适长度的VARCHAR），这样可以避免CAST带来的性能损耗。

内容的提问来源于stack exchange，提问作者SoMZeY