How to rotate a Key / Value style table into a regular table in MySQL

Question

I am trying to convert the following table into something a little easier to query. Historical data is important, therefore it cannot be discarded, and there are an indefinite number of options for Variable (I need only some of them in the final result).

This is almost what I need to do, but it does not take into account historical data and assumes that the variables are unique to User_ID in my case, if user_ID can have 3 or 4 of the same variable and I need the newest one. See> MySQL MySQL Table>

| UUID |UUID_User |Variable |Value |DateSet | |--------|----------|----------|----------|---------------------| | X123Y |123XX12 |FirstName |Jane | 2011-07-09 14:13:12 | | X126Y |123XX12 |LastName |Jones | 2011-07-09 14:13:12 | | X173Y |123XX62 |FirstName |Joe | 2011-07-09 14:11:12 | | X143Y |123XX62 |LastName |Smith | 2011-07-09 14:11:12 | | X129Y |123XX12 |LastName |Smith | 2011-11-09 14:13:12 | << Jane Gets Married 

Convert the above (make sure to use the last entry of the last name for jane)

 |UUID_User |FirstName |LastName | |----------|----------|----------| |123XX12 |Jane |Smith | |123XX62 |John |Smith | 

Answer 1

Retrieving the most recent entry for the user is a common problem that is often tagged with the greatest-n-per-group in Stack Overflow. I suggest creating a VIEW for this, although this is not strictly necessary.

 CREATE VIEW LatestKeyValue AS SELECT k1.* FROM KeyValue AS k1 LEFT OUTER JOIN KeyValue AS k2 ON (k1.UUID_User, k1.Variable) = (k2.UUID_User, k2.Variable) AND k1.DateSet < k2.DateSet WHERE k2.DateSet IS NULL 

You can then expand this for each variable you need in several ways, such as:

 SELECT UUID_User, MAX(CASE Variable WHEN 'FirstName' THEN Value END) AS FirstName, MAX(CASE Variable WHEN 'LastName' THEN Value END) AS LastName FROM LatestKeyValue GROUP BY UUID_User