SQL 动态行转列
在SQL中,动态行转列是指将行数据转换为列数据，并且列的数量或名称在查询执行前是未知的，这通常需要使用动态SQL或特定数据库的PIVOT功能。
（图片来源网络，侵删）
使用CASE WHEN聚合函数（通用方法）
这是最通用的方法,适用于几乎所有SQL数据库：
DECLARE @sql NVARCHAR(MAX)
DECLARE @columns NVARCHAR(MAX)
-- 获取所有可能的列名（假设从某个表中获取）
SELECT @columns = STRING_AGG(QUOTENAME(column_name), ',')
FROM information_schema.columns
WHERE table_name = 'your_table' AND column_name IN ('col1', 'col2', 'col3')
-- 构建动态SQL
SET @sql = N'
SELECT
id,
' + @columns + '
FROM (
SELECT
id,
column_name,
column_value
FROM your_source_table
) AS src
PIVOT (
MAX(column_value)
FOR column_name IN (' + @columns + ')
) AS pvt'
EXEC sp_executesql @sql
使用PIVOT语法（SQL Server/Oracle等）
SQL Server示例
DECLARE @sql NVARCHAR(MAX)
DECLARE @columns NVARCHAR(MAX)
-- 获取所有可能的列名
SELECT @columns = STRING_AGG(QUOTENAME(category), ',')
FROM (
SELECT DISTINCT category FROM products
) AS t
-- 构建动态SQL
SET @sql = N'
SELECT product_id, ' + @columns + '
FROM (
SELECT
product_id,
category,
value
FROM product_values
) AS src
PIVOT (
SUM(value)
FOR category IN (' + @columns + ')
) AS pvt'
EXEC sp_executesql @sql
Oracle示例
DECLARE
v_sql VARCHAR2(4000);
v_columns VARCHAR2(4000);
BEGIN
-- 获取所有可能的列名
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name)
INTO v_columns
FROM all_tab_columns
WHERE table_name = 'YOUR_TABLE';
-- 构建动态SQL
v_sql := 'SELECT * FROM (
SELECT id, column_name, column_value
FROM your_source_table
) PIVOT (
MAX(column_value) FOR column_name IN (' || v_columns || ')
)';
EXECUTE IMMEDIATE v_sql;
END;
使用JSON函数（MySQL 8.0+/PostgreSQL）
MySQL 8.0+ 示例
SET @sql = (SELECT JSON_OBJECTAGG(column_name, column_value)
FROM (
SELECT id, column_name, column_value
FROM your_source_table
) AS t
GROUP BY id);
SET @sql = CONCAT('SELECT id, ', @sql, ' FROM your_source_table');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
PostgreSQL 示例
SELECT id, jsonb_object_agg(column_name, column_value) AS result FROM your_source_table GROUP BY id;
注意事项
- 动态SQL需要处理SQL注入风险,确保对动态部分进行适当的转义或参数化
- 不同数据库的实现方式不同,需要根据具体数据库调整语法
- 对于大数据集,动态行转列可能会影响性能，需要谨慎使用
- 确保在动态SQL中正确处理NULL值和特殊字符
选择哪种方法取决于您使用的具体数据库系统以及您的业务需求。
