Wie transponiere ich eine SQL-Tabelle mit dynamisch generierten Spalten?

SQL-Tabelle transponieren: Erzielen einer flexiblen Datentransformation

Transponierungsvorgänge in SQL umfassen die Konvertierung von Zeilen in Spalten und umgekehrt. Lassen Sie uns in diesem Zusammenhang das Problem der Transponierung einer Tabelle mit benutzerdefinierten Feldnamen für benutzerspezifische Daten angehen.

Problem:

Gegeben sei eine Tabelle mit benutzer- Das Ziel besteht darin, definierte Feldnamen und entsprechende Werte in ein Format zu übertragen, in dem die Daten jedes Benutzers in einer einzelnen Zeile dargestellt werden, wobei die Spaltennamen das Feld darstellen Namen.

Beispiel:

Eingabetabelle:

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]

Gewünschte Ausgabe:

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

Lösung in MySQL (ohne ANSI-Syntax):

Wie MySQL nicht Wenn wir die ANSI PIVOT/UNPIVOT-Syntax unterstützen, können wir einen bedingten Aggregationsansatz verwenden:

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

Dieser Ansatz iteriert über jede Zeile und verwendet CASE-Anweisungen, um den gewünschten Ausgabespalten basierend auf dem Feldnamen Werte zuzuweisen. Anschließend werden die Ergebnisse für jede Benutzer-ID zusammengefasst.

Dynamische Lösung mit Prepared Statements:

Um die Lösung vielseitiger zu gestalten, können wir die Prepared Statement-Syntax von MySQL verwenden, die dies ermöglicht Wir können die Felder dynamisch spezifizieren:

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;

Dieser Ansatz erstellt eine dynamische Abfrage, die alle gewünschten Feldnamen als Spalten in die enthält Ausgabe.

Das obige ist der detaillierte Inhalt vonWie transponiere ich eine SQL-Tabelle mit dynamisch generierten Spalten?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!