首頁 > 資料庫 > mysql教程 > 如何轉置具有動態生成列的 SQL 表?

如何轉置具有動態生成列的 SQL 表?

Barbara Streisand
發布: 2025-01-05 05:03:41
原創
537 人瀏覽過

How to Transpose a SQL Table with Dynamically Generated Columns?

轉置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中文網其他相關文章!

來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
作者最新文章
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板