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?

Solution 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.

Solution 2

You could use GROUP_CONCAT

(untested)

SELECT UserId, 
GROUP_CONCAT( if( fieldname = 'Username', fieldvalue, NULL ) ) AS 'Username', 
GROUP_CONCAT( if( fieldname = 'Password', fieldvalue, NULL ) ) AS 'Password', 
GROUP_CONCAT( if( fieldname = 'Email Address', fieldvalue, NULL ) ) AS 'Email Address', 
FROM table  
GROUP BY UserId