I'm trying to convert rows to columns.
I have the following columns: user_id, meta_key, meta_value
I have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'
Right now I can generate the rows and columns sorted and from a specific user_id
. In my case it's from 15. This is with the following query :
SELECT user_id, meta_key, meta_value
FROM DATA_A ORDER BY user_id DESC;
where meta_key IN ('first_name', 'last_name', 'stillingsbetegnelse')
AND user_id > 15;
And the result is:
user_id meta_key meta_value
16 first_name Laxgdfds
16 last_name Narentdsaa
16 stillingsbetegnelse Andet
18 first_name Riedsa
18 last_name Joe
18 stillingsbetegnelse Andet
(all information is anonymized)
How do I get the following output?
first_name last_name stillingsbetegnelse
Laxgdfds Narentdsaa Andet
Riedsa Joe Andet
I have tried methods with pivot, but I'm getting an error:
select
p.*
from DATA_A
pivot(
max([meta_value])
for [meta_key] in ([first_name], [last_name], [stillingsbetegnelse])
) as p
I think the problem is that I want to take values only from a certain ID. How can I implement this feature? I only want the states data above, but I'm getting a lot of other data, that I don't have any use for. ...........................
With help i found a solution from Stackoverflow. The solution is
> SELECT user_id, MAX(IF(meta_key = 'first_name', meta_value,NULL))
> AS Fornavn, MAX(IF(meta_key = 'last_name', meta_value,NULL)) AS
> Efternavn, MAX(IF(meta_key = 'stillingsbetegnelse',
> meta_value,NULL)) AS FROM DATA_A GROUP BY user_id;
meta_key
values is static? 2) Do NOT use the asterisk in pivot query. 3) UseFROM (SELECT * FROM DATA_A) src
- it seems this table contains excess fields. See FROM - Using PIVOT and UNPIVOT, especially the examples. – Akina Dec 19 '19 at 9:11