MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (x INT, y INT, value INT);
INSERT INTO test VALUES
(762, 635, 1),
(762, 635, 100),
(762, 635, 7),
(762, 762, 9),
(762, 762, 9),
(762, 762, 9),
(762, 762, 9),
(762, 889, 2),
(1016, 635, 1),
(1016, 889, 3),
(1270, 635, 2),
(1270, 762, 3),
(1270, 889, 3),
(1270, 889, 4);
SELECT * FROM test;


x
y
value


762
635
1

762
635
100

762
635
7

762
762
9

762
762
9

762
762
9

762
762
9

762
889
2

1016
635
1

1016
889
3

1270
635
2

1270
762
3

1270
889
3

1270
889
4


CREATE PROCEDURE pivot (tablename VARCHAR(64),
                        groupname VARCHAR(64),
                        pivotname VARCHAR(64),
                        valuename  VARCHAR(64))
BEGIN
SELECT CONCAT('CREATE VIEW to_columnslist AS\n',
              'SELECT DISTINCT CONCAT(\'`\', `', pivotname,'`, \'` VARCHAR(255) path \\\'$."\', ', pivotname,', \'"\\\'\') line\n',
              'FROM ', tablename)
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT CONCAT(
'SELECT to_json.`', groupname,'`, parsed.*', '\n',
'FROM (SELECT `', groupname,'`, JSON_OBJECTAGG(`', pivotname,'`, `', valuename,'`) json_data', '\n',
'      FROM `', tablename,'`', '\n',
'      GROUP BY `', groupname,'`) to_json', '\n',
'CROSS JOIN JSON_TABLE( json_data,', '\n',
'                       "$" COLUMNS ( ', 
GROUP_CONCAT(line SEPARATOR ',\n                                     '),
' ) ) parsed'
) sql_text
INTO @sql
FROM to_columnslist;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
DROP VIEW to_columnslist;
END
CREATE VIEW source_for_pivot
AS 
SELECT x, y, COUNT(value) cnt
FROM test
GROUP BY x, y;
CALL pivot ('source_for_pivot', 'x', 'y', 'cnt');


x
635
762
889


762
3
4
1

1016
1
null
1

1270
1
1
2

x	y	value
762	635	1
762	635	100
762	635	7
762	762	9
762	762	9
762	762	9
762	762	9
762	889	2
1016	635	1
1016	889	3
1270	635	2
1270	762	3
1270	889	3
1270	889	4