I am trying to convert the following table into something the is a bit easier to query. The historical data is important so it cannot get thrown away and there is an indefinite number of options for Variable (I only need some of them in the final result).
This is almost exactly what I need to do but it doesn't account for the historical data and assumes that the Variables are unique to the User_ID in my case a user_ID may have 3 or 4 of the same variable and I need the newest one. See > MySQL Pivot 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
Transform the above into (making sure to use the newest lastname entry for jane)
|UUID_User |FirstName |LastName |
|----------|----------|----------|
|123XX12 |Jane |Smith |
|123XX62 |John |Smith |
Getting the most recent entry per user is a common problem that is often tagged greatest-n-per-group on Stack Overflow. I suggest creating a VIEW for this, though it isn't 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
Then you can pivot that for each variable you need in a couple of different ways such as the following:
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