SQL - How to transpose?

Question

I have something similar to the following 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] | ------------------------------------------------ 

I need a query that will give me a result like this:

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

Note that the values in FieldName are not limited to Username, Password, and Email Address. They can be anything as they are user defined.

Is there a way to do this in SQL?

Answer 1

MySQL doesn't support ANSI PIVOT/UNPIVOT syntax, so that leave you to use:

  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 

As you can see, the CASE statements need to be defined per value. To make this dynamic, you'd need to use MySQL's Prepared Statement (dynamic SQL) syntax.