How to Transpose Data in SQL Using CASE Statements?

Transpose Data in a SQL Table Using Case Statements

In SQL, transposing data refers to converting a dataset where rows and columns are swapped. To transpose a table with a structure similar to the one provided:

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]

into a transposed form:

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

One approach is to use multiple CASE statements along with group by clauses:

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 query calculates the maximum value for each user and field combination. The CASE statements define which field value to return based on the value of the FieldName field.

However, this method requires defining CASE statements for each specific field name. For a dynamic solution, consider using MySQL's Prepared Statement (dynamic SQL) syntax to generate the query.

The above is the detailed content of How to Transpose Data in SQL Using CASE Statements?. For more information, please follow other related articles on the PHP Chinese website!