How to Transpose a SQL Table with Dynamically Generated Columns?

Transpose SQL Table: Achieving a Flexible Data Transformation

Transpose operations in SQL involve converting rows into columns and vice versa. In this context, let's tackle the problem of transposing a table with user-defined field names for user-specific data.

Problem:

Given a table with user-defined field names and corresponding values, the goal is to transpose it into a format where each user's data is presented in a single row, with column names representing the field names.

Example:

Input Table:

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]

Desired Output:

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

Solution in MySQL (Without ANSI Syntax):

As MySQL does not support ANSI PIVOT/UNPIVOT syntax, we can use a conditional aggregation approach:

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

This approach iterates over each row and uses CASE statements to assign values to the desired output columns based on the fieldname. It then aggregates the results for each userId.

Dynamic Solution using Prepared Statements:

To make the solution more versatile, we can use MySQL's Prepared Statement syntax, which allows us to dynamically specify the fields:

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;

This approach creates a dynamic query that includes all the desired field names as columns in the output.

The above is the detailed content of How to Transpose a SQL Table with Dynamically Generated Columns?. For more information, please follow other related articles on the PHP Chinese website!