MariaDB中如何将单行列值转置为列？附查询及结果

方法一：静态条件聚合（已知最大value数量） #

如果提前明确每个分组最多的value条目数（比如示例中最多3条），可以用条件聚合直接生成固定列：

SELECT 
    paracode,
    paramname,
    paramrange,
    MAX(CASE WHEN rn = 1 THEN value END) AS value1,
    MAX(CASE WHEN rn = 2 THEN value END) AS value2,
    MAX(CASE WHEN rn = 3 THEN value END) AS value3
FROM (
    -- 给每个分组内的value添加行号
    SELECT 
        transquantity_parameter AS paracode,
        parameter_name AS paramname,
        -- 假设paramrange字段来自pkgqc_itemparameter表，需根据实际结构调整
        pkgqc_itemparameter.paramrange,
        transquantity_value AS value,
        -- 若要保证顺序稳定，建议替换为表中实际排序字段（如主键）
        ROW_NUMBER() OVER (PARTITION BY transquantity_parameter ORDER BY (SELECT NULL)) AS rn
    FROM pkgqc_transquantity 
    INNER JOIN pkgqc_itemparameter ON parameter_code = transquantity_parameter 
    WHERE transquantity_headerno = '9405000143' AND transquantity_type = 'QN'
) AS base_data
GROUP BY paracode, paramname, paramrange
ORDER BY paracode;

方法二：动态SQL（自动适配任意数量的value） #

如果分组的value数量不固定，可以用动态SQL自动生成对应列，通过存储过程实现：

DELIMITER //

CREATE PROCEDURE pivot_transquantity_values()
BEGIN
    -- 获取分组内最多的value数量
    SELECT MAX(value_count) INTO @max_col_count
    FROM (
        SELECT COUNT(*) AS value_count
        FROM pkgqc_transquantity 
        INNER JOIN pkgqc_itemparameter ON parameter_code = transquantity_parameter 
        WHERE transquantity_headerno = '9405000143' AND transquantity_type = 'QN'
        GROUP BY transquantity_parameter
    ) AS count_result;

    -- 动态生成CASE语句片段
    SET @case_clauses = '';
    SET @current_idx = 1;
    WHILE @current_idx <= @max_col_count DO
        SET @case_clauses = CONCAT(@case_clauses, ', MAX(CASE WHEN rn = ', @current_idx, ' THEN value END) AS value', @current_idx);
        SET @current_idx = @current_idx + 1;
    END WHILE;

    -- 拼接完整查询语句
    SET @full_sql = CONCAT(
        'SELECT paracode, paramname, paramrange', @case_clauses, '
        FROM (
            SELECT 
                transquantity_parameter AS paracode,
                parameter_name AS paramname,
                pkgqc_itemparameter.paramrange,
                transquantity_value AS value,
                ROW_NUMBER() OVER (PARTITION BY transquantity_parameter ORDER BY (SELECT NULL)) AS rn
            FROM pkgqc_transquantity 
            INNER JOIN pkgqc_itemparameter ON parameter_code = transquantity_parameter 
            WHERE transquantity_headerno = ''9405000143'' AND transquantity_type = ''QN''
        ) AS base_data
        GROUP BY paracode, paramname, paramrange
        ORDER BY paracode;'
    );

    -- 执行动态SQL
    PREPARE stmt FROM @full_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用存储过程得到转置结果
CALL pivot_transquantity_values();

方法三：用GROUP_CONCAT合并为单个字段（快速实现类似格式） #

如果可以接受将多个value合并为一个空格分隔的字段，这个方法更简洁：

SELECT 
    transquantity_parameter AS paracode,
    parameter_name AS paramname,
    pkgqc_itemparameter.paramrange,
    -- 用空格分隔多个value，可调整SEPARATOR的字符
    GROUP_CONCAT(transquantity_value SEPARATOR '     ') AS values_list
FROM pkgqc_transquantity 
INNER JOIN pkgqc_itemparameter ON parameter_code = transquantity_parameter 
WHERE transquantity_headerno = '9405000143' AND transquantity_type = 'QN'
GROUP BY transquantity_parameter, parameter_name, paramrange
ORDER BY transquantity_parameter;

注意事项 #

• 若需要保证value的顺序与原查询一致，建议将ROW_NUMBER()中的ORDER BY (SELECT NULL)替换为表中的实际排序字段（如主键、创建时间等），避免顺序混乱。
• paramrange字段的来源需根据你的实际表结构调整，确保字段存在于关联的表中。

内容的提问来源于stack exchange，提问作者Mohan Lal Mourya