32

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441
  • see http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema for a similar question. Is it impossible to do the pivoting in the application and leave the query straight forward? If you will need N attributes, you will need to generate the query dynamically. – Amitay Dobo Aug 03 '10 at 02:05

2 Answers2

56

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • @KLee - So he doesn't have to group by those columns – dcp Aug 03 '10 at 01:21
  • 6
    @KLee1: Because the case statement doesn't flat the query - there'll be nulls in various spots. So you have to use GROUP BY to flatten the query - MAX takes the highest value, and anything is higher than NULL. – OMG Ponies Aug 03 '10 at 01:39
  • 2
    +1. Tricky. Wish I was good enough at MySQL to do something like that. – KLee1 Aug 03 '10 at 02:22
1

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
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506