"Unknown column" error when trying to convert EAV table to regular view

Question

My MySQL "data" EAV table has a genetic structure like that:

rowId  |  key |  value
  1       name    John
  1       lname   Lennon
  2       name    Paul
 ....

I like to do a select that will display the data in the table as if the keys were the column names:

rowId  | name |  lname
  1      John     Lennon
  2      Paul
  ....

Following some links like this one, I created this query:

SELECT st.rowId, st.key, st.value,
  (CASE WHEN data.key = "name" THEN data.value END) AS `name`, 
  (CASE WHEN data.key = "lname" THEN data.value END) AS `lname`
FROM data st 
GROUP BY st.rowId;

But I get the following error:

Unknown column 'data.key' in 'field list'

I tried to change the " sign as suggested here but no success yet.

Answer 1

A number of errors 1) you have aliased data to st so you should reference st for all the columns (you cannot mix and match) 2) You have columns in the select which make no sense 3) you need a max function

SELECT st.rowId ,#, st.key, st.value,
  max(CASE WHEN st.key = "name" THEN st.value END) AS `name`, 
  max(CASE WHEN st.key =  'lname' THEN st.value END) AS `lname`
FROM  data st
GROUP BY st.rowId;

+-------+------+--------+
| rowId | name | lname  |
+-------+------+--------+
|     1 | John | Lennon |
|     2 | Paul | NULL   |
+-------+------+--------+
2 rows in set (0.00 sec)