the db is structured as follows:
id | meta-key | meta-value
1 | client | John
1 | bday | today
i want it to be
id | client | bday
1 | John | today
there are many lines like client and bday and i want to transform some of them into columns either when displaying or inserting into another table
i tried something like this:
SELECT p1.meta_value, wph0_posts.post_title, p2.meta_value as client, p3.meta_value as end_date, p4.meta_value as description
FROM wph0_postmeta p1
INNER JOIN wph0_posts ON p1.post_id = wph0_posts.ID
INNER JOIN wph0_postmeta p2 ON
p1.post_id = p2.post_id AND
p2.meta_key = 'client'
INNER JOIN wph0_postmeta p3 ON
p1.post_id = p3.post_id AND
p3.meta_key = 'end_date'
INNER JOIN wph0_postmeta p4 ON
p1.post_id = p4.post_id AND
p4.meta_key = 'description';
can i get some tips? pretty new to db'es
CREATE TABLE wph0_postmeta ( `id` INTEGER, `meta-key` VARCHAR(20), `meta-value` VARCHAR(20) ); INSERT INTO wph0_postmeta (`id`, `meta-key`, `meta-value`) VALUES ('1', 'client', 'John'), ('1', 'bday', 'today'), ('1', 'descriptiuon', 'test'), ('2', 'client', 'Jerry'), ('2', 'bday', 'tomorrow'), ('2', 'descriptiuon', 'secondtest');
INSERT INTO wph0_postmeta (`id`, `meta-key`, `meta-value`) VALUES ('1', 'client', 'John'), ('1', 'bday', 'today'), ('1', 'descriptiuon', 'test'), ('2', 'client', 'Jerry'), ('2', 'bday', 'tomorrow'), ('2', 'descriptiuon', 'secondtest');
CREATE TABLE wph0_posts ( id INT, post_title varchar(20)); INSERT INTO wph0_posts VALUES (1,'Dr.Dr.'),(2,'Prof.Dr.Dr.')
SELECT t1.id, t2.post_title ,MAX(IF(t1.`meta-key` = 'client', t1.`meta-value`,NULL)) 'client' ,MAX(IF(t1.`meta-key` = 'bday', t1.`meta-value`,NULL)) 'birthday' ,MAX(IF(t1.`meta-key` = 'descriptiuon', t1.`meta-value`,NULL)) 'descriptiuon' FROM wph0_postmeta t1 INNER JOIN wph0_posts t2 ON t1.id = t2.id GROUP BY t1.id,t2.post_titleid | post_title | client | birthday | descriptiuon -: | :---------- | :----- | :------- | :----------- 1 | Dr.Dr. | John | today | test 2 | Prof.Dr.Dr. | Jerry | tomorrow | secondtest
db<>fiddle here
This works for a fixed set of value perfect. but of you have much more and deffrent you must look at MySQL query to create a pivot table by joining 4 different table
Thx for the help, the following worked:
SELECT wph0_postmeta.post_id,
GROUP_CONCAT( if(wph0_postmeta.meta_key='client',wph0_postmeta.meta_value,NULL) ) AS Nume,
GROUP_CONCAT( if(wph0_postmeta.meta_key='end_date',wph0_postmeta.meta_value,NULL) ) AS Descriere
FROM wph0_postmeta GROUP BY wph0_postmeta.post_id
i only extracted the name and some stuff from wph0_postmeta and used the if statement . I can now add another group_concat and add another column from a meta_key, i got the link for pivots here: