Bagaimana untuk Mengubah Jadual SQL dengan Lajur Dijana Secara Dinamik?

Transpose SQL Table: Mencapai Transformasi Data Fleksibel

Transpose operasi dalam SQL melibatkan penukaran baris kepada lajur dan sebaliknya. Dalam konteks ini, mari kita atasi masalah pemindahan jadual dengan nama medan yang ditentukan pengguna untuk data khusus pengguna.

Masalah:

Diberikan jadual dengan pengguna- nama medan yang ditentukan dan nilai yang sepadan, matlamatnya adalah untuk mengubahnya ke dalam format yang mana setiap data pengguna dibentangkan dalam satu baris, dengan lajur nama yang mewakili nama medan.

Contoh:

Jadual Input:

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]

Output yang Diingini:

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

Penyelesaian dalam MySQL (Tanpa ANSI Sintaks):

Memandangkan MySQL tidak menyokong sintaks ANSI PIVOT/UNPIVOT, kami boleh menggunakan pendekatan pengagregatan bersyarat:

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

Pendekatan ini berulang pada setiap baris dan menggunakan pernyataan CASE untuk memberikan nilai kepada lajur keluaran yang dikehendaki berdasarkan nama medan. Ia kemudiannya mengagregatkan keputusan untuk setiap userId.

Penyelesaian Dinamik menggunakan Penyata Disediakan:

Untuk menjadikan penyelesaian lebih serba boleh, kami boleh menggunakan sintaks Penyata Disediakan MySQL, yang membolehkan kami untuk menentukan medan secara dinamik:

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;

Pendekatan ini mewujudkan pertanyaan dinamik yang merangkumi semua nama medan yang diingini sebagai lajur dalam output.

Atas ialah kandungan terperinci Bagaimana untuk Mengubah Jadual SQL dengan Lajur Dijana Secara Dinamik?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!