如何在SQL查询中转换和转置非数值型数据

嘿，这个需求我之前帮不少人处理过，刚好在SQL里处理这类重复customer_id转唯一行+非数值型数据转置的场景，其实分静态和动态两种情况来搞就很清晰，我给你一步步拆解～

先假设你的原始数据表是这样的（拿常见的用户属性表举例）：

我们的目标是把每个customer_id的多条记录合并成一行，把attribute_name转成列，对应attribute_value填进去。

一、静态转置（已知要转置的列名） #

如果你的attribute_name是固定的（比如只有“性别”“城市”这几个），直接用硬编码的方式就能快速实现，不同SQL方言的写法略有不同：

1. MySQL / MariaDB #

因为这俩没有内置的转置函数，用CASE WHEN配合聚合函数就能搞定：

SELECT
  customer_id,
  MAX(CASE WHEN attribute_name = '性别' THEN attribute_value END) AS 性别,
  MAX(CASE WHEN attribute_name = '城市' THEN attribute_value END) AS 城市
FROM customer_attributes
GROUP BY customer_id;

这里用MAX是因为分组后每个customer_id对应多条记录，MAX会自动忽略NULL值（不满足CASE条件的行返回NULL），最终只保留有效属性值。如果每个customer_id对每个属性只有一条记录，用MIN也可以，效果一致。

2. PostgreSQL #

PostgreSQL有专门的crosstab函数，但需要先启用tablefunc扩展：

-- 先启用扩展（只需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 执行转置查询
SELECT * FROM crosstab(
  'SELECT customer_id, attribute_name, attribute_value FROM customer_attributes ORDER BY 1,2',
  'SELECT DISTINCT attribute_name FROM customer_attributes ORDER BY 1'
) AS ct(customer_id INT, 性别 TEXT, 城市 TEXT);

crosstab的第一个参数是原始数据查询，第二个参数是指定要转成列的属性名，最后要定义结果集的列结构。

3. SQL Server #

SQL Server内置了PIVOT操作符，写法更简洁：

SELECT customer_id, [性别], [城市]
FROM (
  -- 子查询先取出需要转置的基础数据
  SELECT customer_id, attribute_name, attribute_value
  FROM customer_attributes
) AS src
PIVOT (
  -- 非数值型用MAX/MIN做聚合，因为PIVOT必须指定聚合函数
  MAX(attribute_value) FOR attribute_name IN ([性别], [城市])
) AS pvt;

二、动态转置（未知要转置的列名） #

如果你的attribute_name是动态变化的（比如随时会新增“年龄”“职业”这类属性），静态硬编码就不适用了，得用动态SQL自动生成列名：

1. MySQL 动态转置示例 #

-- 第一步：拼接所有属性名对应的CASE语句
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN attribute_name = ''', attribute_name, ''' THEN attribute_value END) AS `', attribute_name, '`')) INTO @cols
FROM customer_attributes;

-- 第二步：拼接完整的查询语句
SET @query = CONCAT('SELECT customer_id, ', @cols, ' FROM customer_attributes GROUP BY customer_id');

-- 第三步：执行动态SQL
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这里用GROUP_CONCAT把所有属性名自动拼接成CASE语句，然后动态执行生成的SQL，不管新增多少属性都能自动适配。

2. SQL Server 动态转置示例 #

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 第一步：拼接所有属性名（用QUOTENAME处理特殊字符）
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(attribute_name)
                      FROM customer_attributes
                      GROUP BY attribute_name
                      ORDER BY attribute_name
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 第二步：拼接完整的PIVOT查询
SET @query = 'SELECT customer_id, ' + @cols + '
              FROM (
                    SELECT customer_id, attribute_name, attribute_value
                    FROM customer_attributes
                   ) AS src
              PIVOT (
                     MAX(attribute_value)
                     FOR attribute_name IN (' + @cols + ')
                    ) AS pvt';

-- 第三步：执行动态SQL
EXECUTE sp_executesql @query;

用STUFF和FOR XML PATH来拼接列名，同样能自动适配新增的属性。

三、非数值型数据的转换技巧 #

如果你的attribute_value需要转换成特定类型（比如把字符串日期转成DATE类型，或者把“是/否”转成布尔值），可以在CASE语句里先做转换：

-- MySQL示例：把字符串格式的注册日期转成DATE类型
SELECT
  customer_id,
  MAX(CASE WHEN attribute_name = '注册日期' THEN STR_TO_DATE(attribute_value, '%Y-%m-%d') END) AS 注册日期
FROM customer_attributes
GROUP BY customer_id;

不同数据库的转换函数不一样：

• MySQL: STR_TO_DATE()、CAST()
• PostgreSQL: TO_DATE()、::DATE
• SQL Server: CONVERT()、TRY_CONVERT()

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