make dynamic matrix query in mysql

Question

I want to make a matrix query from these data

name    distance
roby    10
dina    5
axel    6
john    8

and I make the query like this :

SELECT tabel1.name, tabel2.name,tabel1.distance,tabel2.distance  
FROM passenger tabel1 
LEFT JOIN passenger tabel2 ON tabel1.name != tabel2.name AND tabel1.id < tabel2.id  
WHERE tabel2.name IS NOT NULL 
ORDER BY tabel1.name ASC, tabel2.name ASC

and the result is like this :

name    name    distance    distance
axel    john       6           8
dina    axel       5           6
dina    john       5           8
roby    axel       10          6
roby    dina       10          5
roby    john       10          8

but I want the result is like this :

       roby    dina    axel    john
roby    0       10      10      10
dina    5       0       6       8
axel    6       5       0       8
john    8       5       6       0

Is it possible to do that?

Answer 1

You can do like this:

SET @sql = NULL;
SELECT
   GROUP_CONCAT(DISTINCT
     CONCAT(
        'IF(a.distance = ''',
            distance,
            ''', 0, a.distance) AS "',
         name,'"'
     )
   ) INTO @sql
FROM your_table;




SET @sql = CONCAT('SELECT a.name,' ,@sql, ' 
               FROM your_table AS a
               LEFT JOIN your_table AS b
                ON a.name=b.name GROUP BY a.name,b.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Query is update according your desired result. fiddle link