MySQL动态数据透视视图创建：仓库名称不固定时的实现方法

嘿，我来帮你搞定这个动态Pivot的需求！因为仓库名称不固定，静态的Pivot语句肯定行不通，得用MySQL的动态SQL来实现。下面是一步步的解决方案：

第一步：先从Warehouse字段提取干净的仓库名称 #

原始的Warehouse字段格式比较复杂，比如103-VAN DXB- U56403 NADEEM - DLTL，我们需要提取出NADEEM；100-Main Warehouse - Nahda - DLTL要提取出Main Warehouse。可以用字符串处理函数组合来实现：

-- 测试提取逻辑
SELECT 
    item_code,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(warehouse, '-', -2), '-', 1)) AS warehouse_name,
    stock_value
FROM your_table;

这段代码的逻辑是：先取倒数第二个-之后的内容，再取第一个-之前的部分，最后用TRIM去掉多余空格，就能得到我们需要的仓库别名了。

第二步：动态生成Pivot列 #

我们需要自动获取所有唯一的仓库名称，然后把它们转换成Pivot需要的CASE语句。用GROUP_CONCAT来拼接这些语句：

SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN warehouse_name = ''',
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(warehouse, '-', -2), '-', 1)),
            ''' THEN 1 ELSE 0 END) AS ''',
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(warehouse, '-', -2), '-', 1)),
            ''''
        )
    ) INTO @sql
FROM your_table;

这段代码会把每个仓库名称转换成类似MAX(CASE WHEN warehouse_name = 'Main Warehouse' THEN 1 ELSE 0 END) AS 'Main Warehouse'的语句，自动适配所有存在的仓库。

第三步：拼接完整的动态SQL并执行 #

把上面生成的Pivot列，加上总数量、总价值的计算，拼成完整的查询语句，然后执行：

SET @sql = CONCAT(
    'SELECT 
        item_code AS item,
        ', @sql, ',
        COUNT(*) AS `total quantity`,
        SUM(stock_value) AS `total value`
    FROM (
        SELECT 
            item_code,
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(warehouse, ''-'', -2), ''-'', 1)) AS warehouse_name,
            stock_value
        FROM your_table
    ) AS temp
    GROUP BY item_code
    ORDER BY item_code;'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• 记得把代码里的your_table替换成你实际的表名。
• 如果Warehouse字段的格式有变动，需要调整字符串提取的逻辑，确保能正确拿到仓库名称。
• 如果仓库数量很多，可能会触发GROUP_CONCAT的长度限制，可以先执行SET SESSION group_concat_max_len = 1000000;来扩大限制。

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