I am querying from a table with the following format:
id|provider|score
--------------------------------
1 | att | '{"attscore":300}'
1 | verizon | '{"verizonscore":299}'
2 | att | '{"attscore":200}'
3 | verizon | '{"verizonscore":155}'
I am trying to get a table that looks like the following:
id|attscore|verizonscore
-------------------------
1 | 300 | 299
2 | 200 | null
3 | null | 155
Note that used to json in sql
CREATE TABLE table1 ( `id` INTEGER, `provider` VARCHAR(7), `score` VARCHAR(22) ); INSERT INTO table1 (`id`, `provider`, `score`) VALUES ('1', 'att', '{"attscore":300}'), ('1', 'verizon', '{"verizonscore":299}'), ('2', 'att', '{"attscore":200}'), ('3', 'verizon', '{"verizonscore":155}');
SELECT id, GROUP_CONCAT(CASE WHEN provider = 'att' THEN `score`->"$.attscore" ELSe NULL END) attscore ,GROUP_CONCAT(CASE WHEN provider = 'verizon' THEN `score`->"$.verizonscore" ELSe NULL END) verizonscore FROM table1 GROUP BY idid | attscore | verizonscore -: | :------- | :----------- 1 | 300 | 299 2 | 200 | null 3 | null | 155
db<>fiddle here
This works with a fixed number of column quite well, if you have much more of these you need to do something like this