By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 id
| id | attscore | verizonscore |
|---|---|---|
| 1 | 300 | 299 |
| 2 | 200 | null |
| 3 | null | 155 |