如何轉置具有動態生成列的 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中文網其他相關文章！