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