How Can I Transpose SQL Table Data with Dynamically Generated User-Defined Column Names?

Transposing SQL Table Data with User-Defined Field Names

Pivoting data, or transposing it, involves transforming a table where columns represent attributes and rows represent entity data into a table where rows represent attributes and columns represent entity data. This is often useful when working with user-defined field names, as seen in the example table provided.

Query for Transposed Table

To transpose the given table, you can use a combination of SQL functions and conditional statements, as shown below:

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 t
GROUP BY t.UserId

Explanation

• The MAX function returns the maximum value for each column, which corresponds to the field value for the specific field name.
• The CASE statement evaluates the FieldName column and returns the corresponding FieldValue for each field name that appears in the WHERE clause.
• The GROUP BY clause groups the results by UserId, ensuring that each user's data is placed in a separate row.

Dynamic Query with Prepared Statements

To handle a dynamic list of field names, you can utilize MySQL's Prepared Statement (dynamic SQL) syntax:

SET @sql = 'SELECT UserId, ';
SELECT GROUP_CONCAT(CONCAT('MAX(CASE WHEN FieldName = ''', FieldName, ''' THEN FieldValue ELSE NULL END) AS ', FieldName)) INTO @sql FROM (SELECT DISTINCT FieldName FROM t);
SET @sql = CONCAT(@sql, ' FROM t GROUP BY UserId');
PREPARE stmt FROM @sql;
EXECUTE stmt;

This query dynamically builds an SQL statement based on the distinct field names in the table and executes it using a prepared statement.

The above is the detailed content of How Can I Transpose SQL Table Data with Dynamically Generated User-Defined Column Names?. For more information, please follow other related articles on the PHP Chinese website!