如何转置具有动态生成列的 SQL 表？

转置 SQL 表：实现灵活的数据转换

SQL 中的转置操作涉及将行转换为列，反之亦然。在这种情况下，让我们解决将具有用户定义字段名称的表转置为用户特定数据的问题。

问题：

给定一个带有 user- 的表定义的字段名称和相应的值，目标是将其转换为一种格式，其中每个用户的数据显示在单行中，列名称代表字段

示例：

输入表：

Id	UserId	FieldName	FieldValue
1	100	Username	John Doe
2	100	Password	pass123!
3	102	Username	Jane
4	102	Password	$ecret
5	102	Email Address	[email protected]

所需输出：

UserId	Username	Password	Email Address
100	John Doe	pass123!	NULL
102	Jane	$ecret	[email protected]

解决方案在 MySQL 中（没有 ANSI语法):

由于 MySQL 不支持 ANSI PIVOT/UNPIVOT 语法，我们可以使用条件聚合方法：

SELECT t.userid,
       MAX(CASE WHEN t.fieldname = 'Username' THEN t.fieldvalue ELSE NULL END) AS Username,
       MAX(CASE WHEN t.fieldname = 'Password' THEN t.fieldvalue ELSE NULL END) AS Password,
       MAX(CASE WHEN t.fieldname = 'Email Address' THEN t.fieldvalue ELSE NULL END) AS Email
FROM TABLE t
GROUP BY t.userid

这种方法会迭代每一行并使用 CASE 语句根据字段名称将值分配给所需的输出列。然后它聚合每个 userId 的结果。

使用预准备语句的动态解决方案：

为了使解决方案更加通用，我们可以使用 MySQL 的预准备语句语法，它允许我们动态指定字段：

SET @query = "SELECT userid, ";
SET @enum_query = "";
SELECT CONCAT(@enum_query, "MAX(CASE WHEN fieldname = '", fieldname, "' THEN fieldvalue ELSE NULL END) AS ", fieldname,", ")
INTO @enum_query
FROM (
    SELECT DISTINCT fieldname
    FROM TABLE
) AS subquery;
SET @enum_query = SUBSTRING(@enum_query, 1, LENGTH(@enum_query) - 2);
SET @query = CONCAT(@query, @enum_query, " FROM TABLE GROUP BY userid;");
PREPARE stmt FROM @query;
EXECUTE stmt;

此方法创建一个动态查询，其中包含所有所需的字段名称作为列输出。

以上是如何转置具有动态生成列的 SQL 表？的详细内容。更多信息请关注PHP中文网其他相关文章！