"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.

score 1 · Accepted Answer · answered Dec 03 '19 at 09:40

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)

answered Dec 03 '19 at 09:40

P.Salmon

17,104
2
12
19

Well, you need an aggregate function (if handling it this way in sql). But it doesn't need to be MAX. MIN would work too. – Strawberry Dec 03 '19 at 09:46
works perfectly! What does the '#' in the select is for? – riorio Dec 03 '19 at 09:50
It's a start of comment indicator. https://dev.mysql.com/doc/refman/8.0/en/comments.html – P.Salmon Dec 03 '19 at 09:52

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

1 Answers1