Tutorial :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 | jane@email.com |  ------------------------------------------------  

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

==================================================  | UserId | Username  | Password | Email Address  |  =========+===========+===========================|  | 100    | John Doe  | pass123! |                |  |--------+-----------+----------+----------------|  | 102    | Jane      | $ecret   | jane@email.com |  |--------+-----------+----------+----------------|  

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »