Snowflake动态转置：将单列数据按规则转为多行多列并填充空值

核心思路 #

• 给每个ID下的Cust_Values按顺序编号，通过计算确定每个值在转置后的行位置和列位置
• 利用动态SQL自动生成对应数量的列，无需硬编码列名
• 用空值填充参数替换转置后的空列，保证输出格式统一

SQL Server 实现方案 #

-- 配置参数，按需修改
DECLARE @values_per_row INT = 2;    -- 每行容纳的值数量
DECLARE @col_prefix NVARCHAR(50) = 'CV_'; -- 输出列的前缀
DECLARE @filler_value NVARCHAR(50) = 'XX'; -- 空值填充内容
DECLARE @max_cols INT;
DECLARE @col_defs NVARCHAR(MAX);
DECLARE @pivot_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 计算每个ID最多需要的列数
SELECT @max_cols = CEILING(MAX(cnt) / CAST(@values_per_row AS FLOAT))
FROM (
    SELECT Cust_ID, COUNT(*) AS cnt
    FROM YourCustomerTable -- 替换为你的实际表名
    GROUP BY Cust_ID
) t;

-- 生成转置后的列定义（包含空值填充）
SET @col_defs = STUFF((
    SELECT ',' + QUOTENAME(@col_prefix + CAST(n AS NVARCHAR(10))) + ' = ISNULL(' + QUOTENAME(@col_prefix + CAST(n AS NVARCHAR(10))) + ', ''' + @filler_value + ''')'
    FROM (
        SELECT TOP(@max_cols) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
        FROM master..spt_values
    ) t
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 生成PIVOT需要的列列表
SET @pivot_cols = STUFF((
    SELECT ',' + QUOTENAME(n)
    FROM (
        SELECT TOP(@max_cols) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
        FROM master..spt_values
    ) t
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接并执行动态SQL
SET @sql = N'
WITH RankedData AS (
    SELECT 
        Cust_ID,
        Cust_Values,
        -- 计算转置后的行编号
        (ROW_NUMBER() OVER(PARTITION BY Cust_ID ORDER BY (SELECT NULL)) - 1) / ' + CAST(@values_per_row AS NVARCHAR(10)) + ' + 1 AS RowNum,
        -- 计算当前值对应的列位置
        (ROW_NUMBER() OVER(PARTITION BY Cust_ID ORDER BY (SELECT NULL)) - 1) % ' + CAST(@values_per_row AS NVARCHAR(10)) + ' + 1 AS ColNum
    FROM YourCustomerTable
),
PivotedResult AS (
    SELECT 
        Cust_ID,
        RowNum,
        ' + REPLACE(@col_defs, ' = ISNULL(', ', ') + ' -- 先获取原始转置值
    FROM RankedData
    PIVOT (
        MAX(Cust_Values)
        FOR ColNum IN (' + @pivot_cols + ')
    ) p
)
SELECT 
    Cust_ID,
    ' + @col_defs + ' -- 替换空值后输出
FROM PivotedResult
ORDER BY Cust_ID, RowNum;
';

EXEC sp_executesql @sql;

MySQL 实现方案（无PIVOT语法） #

-- 配置参数，按需修改
SET @values_per_row = 2;
SET @col_prefix = 'CV_';
SET @filler_value = 'XX';
SET @max_cols = 0;
SET @col_list = '';

-- 计算最大需要的列数
SELECT CEILING(MAX(cnt) / @values_per_row) INTO @max_cols
FROM (
    SELECT Cust_ID, COUNT(*) AS cnt
    FROM YourCustomerTable -- 替换为你的实际表名
    GROUP BY Cust_ID
) t;

-- 循环生成列的聚合逻辑
SET @i = 1;
WHILE @i <= @max_cols DO
    SET @col_list = CONCAT(
        @col_list,
        ', IFNULL(MAX(CASE WHEN ColNum = ', @i, ' THEN Cust_Values END), ''', @filler_value, ''') AS ', @col_prefix, @i
    );
    SET @i = @i + 1;
END WHILE;
SET @col_list = SUBSTRING(@col_list, 2); -- 去掉开头的逗号

-- 拼接并执行动态SQL
SET @sql = CONCAT('
WITH RankedData AS (
    SELECT 
        Cust_ID,
        Cust_Values,
        FLOOR((ROW_NUMBER() OVER(PARTITION BY Cust_ID ORDER BY NULL) - 1) / ', @values_per_row, ') + 1 AS RowNum,
        MOD((ROW_NUMBER() OVER(PARTITION BY Cust_ID ORDER BY NULL) - 1), ', @values_per_row, ') + 1 AS ColNum
    FROM YourCustomerTable
)
SELECT 
    Cust_ID,
    ', @col_list, '
FROM RankedData
GROUP BY Cust_ID, RowNum
ORDER BY Cust_ID, RowNum;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

配置说明 #

• 修改@values_per_row：比如设为3，就会将每个ID的Cust_Values按3个一组拆分到多行
• 修改@col_prefix：比如改为VAL_，输出列名就会变成VAL_1、VAL_2等
• 修改@filler_value：可以设置为空字符串''、'N/A'等自定义填充内容

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