-1

I bet my title is not clear so there is a table of what I want.

I have this :

+------+---------+-------+
|LOGIN |   KEY   | VALUE |
+------+---------+-------+
|JDE001|LASTNAME |Doe    |
|JDE001|FIRSTNAME|John   |
|RRE001|LASTNAME |Roe    |
|RRE001|FIRSTNAME|Richard|
+------+---------+-------+

I'd like :

    +------+--------+---------+
    |LOGIN |LASTNAME|FIRSTNAME|
    +------+--------+---------+
    |JDE001|Doe     |John     |
    |RRE001|Roe     |Richard  |
    +------+--------+---------+

Any ideas ?

Tzoreol
  • 48
  • 2
  • 8

1 Answers1

1

You can use a simple pivot query here. The trick is that for each pair of records belonging to a given login we pivot out the first and last name into separate columns using MAX() along with a CASE expression.

SELECT
    LOGIN,
    MAX(CASE WHEN `KEY`='LASTNAME'  THEN VALUE END) AS LASTNAME,
    MAX(CASE WHEN `KEY`='FIRSTNAME' THEN VALUE END) AS FIRSTNAME
FROM yourTable
GROUP BY LOGIN

Output:

enter image description here

Demo here:

Rextester

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • How many times are you going to answer these questions rather than closing them down as duplicate? – Shadow Jul 19 '17 at 14:33
  • @Shadow The data looked real to me, so I thought it wasn't a homework dump. Granted, little effort was shown, but it seemed like a real question. – Tim Biegeleisen Jul 19 '17 at 14:34
  • It does not matter whether a question is homework or a real one - if it's been answered before, then it is a duplicate. This is the 4th mysql pivot table question I have encountered today. – Shadow Jul 19 '17 at 14:46