SQL行转列实现方案：关联AttributeType与AttributeValue表动态生成输出列

EAV模型行转列SQL实现方案 #

静态写法（属性固定，兼容所有主流数据库） #

如果你当前的属性类型固定为Name/Address/Amount三个，直接用通用CASE聚合写法即可：

SELECT 
  av.ID,
  MAX(CASE WHEN at.Name = 'Name' THEN av.Value END) AS Name,
  MAX(CASE WHEN at.Name = 'Address' THEN av.Value END) AS Address,
  MAX(CASE WHEN at.Name = 'Amount' THEN av.Value END) AS Amount
FROM AttributeValue av
INNER JOIN AttributeType at 
  ON av.AttributeId = at.ID
GROUP BY av.ID;

执行后直接输出你需要的结果：

动态写法（属性数量可变，自动适配AttributeType的字段） #

如果后续AttributeType会新增属性，不需要修改SQL结构，直接用对应数据库的动态SQL语法实现即可，不同数据库写法如下：

MySQL版本

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN at.Name = ''', Name, ''' THEN av.Value END) AS ', Name)
) INTO @sql FROM AttributeType;

SET @sql = CONCAT('SELECT av.ID, ', @sql, ' FROM AttributeValue av JOIN AttributeType at ON av.AttributeId = at.ID GROUP BY av.ID');

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

PostgreSQL版本

CREATE OR REPLACE FUNCTION dynamic_eav_pivot()
RETURNS TABLE (id INT, "Name" TEXT, "Address" TEXT, "Amount" TEXT) AS $$
DECLARE
    cols TEXT;
BEGIN
    SELECT STRING_AGG(DISTINCT format('MAX(CASE WHEN at.Name = %L THEN av.Value END) AS %I', "Name", "Name"), ', ')
    INTO cols FROM AttributeType;
    RETURN QUERY EXECUTE format('SELECT av.ID, %s FROM AttributeValue av JOIN AttributeType at ON av.AttributeId = at.ID GROUP BY av.ID', cols);
END;
$$ LANGUAGE plpgsql;

-- 调用获取结果
SELECT * FROM dynamic_eav_pivot();

SQL Server版本

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT DISTINCT ', MAX(CASE WHEN at.Name = ''' + Name + ''' THEN av.Value END) AS ' + QUOTENAME(Name)
                    FROM AttributeType
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT av.ID, ' + @cols + ' FROM AttributeValue av JOIN AttributeType at ON av.AttributeId = at.ID GROUP BY av.ID'
EXECUTE sp_executesql @query;

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