dimasadhipradana dimasadhipradana - 2 months ago 12
MySQL Question

how to create a matrix of pairwise pivot table in mysql

i have data like this :

id_jarak idkota1 idkota2 jarak
1 1 2 1
2 1 3 2
3 1 4 3
4 2 1 1
5 2 3 5
8 3 5 8
7 3 2 5
6 3 1 2
9 4 1 3
10 4 5 9
11 5 3 8
12 5 4 9


and i try to make pivot and matrix from that data
this my query

SELECT a.idkota1,
( IF(a.idkota2 = '1' or b.idkota1 = '1', a.jarak, 0 ) ) AS 1s,
( IF(a.idkota2 = '2' or b.idkota1 = '2', a.jarak, 0 ) ) AS 2s,
( IF(a.idkota2 = '3' or b.idkota1 = '3', a.jarak, 0 ) ) AS 3s,
( IF(a.idkota2 = '4' or b.idkota1 = '4', a.jarak, 0 ) ) AS 4s,
( IF(a.idkota2 = '5' or b.idkota1 = '5', a.jarak, 0 ) ) AS 5s

FROM ms_jarak_kota as a
inner join ms_jarak_kota as b
on a.idkota1>=b.idkota2
GROUP BY b.idkota2


but result show like this

idkota1 1s 2s 3s 4s 5s
1 0 1 0 0 0
2 1 0 0 0 0
3 2 0 0 0 0
4 3 0 0 0 0
5 0 0 8 0 0


my expectation result is like this

1s 2s 3s 4s 5s
1 0 1 2 3
2 1 0 5
3 2 5 0 8
4 3 0 9
5 8 9 0


i thing something wrong with my query, how to fix this? thanks

Answer

Try this way:

SELECT idkota1, 
       MAX(CASE WHEN idkota2 = 1 THEN jarak END) AS '1s',
       MAX(CASE WHEN idkota2 = 2 THEN jarak END) AS '2s',
       MAX(CASE WHEN idkota2 = 3 THEN jarak END) AS '3s',
       MAX(CASE WHEN idkota2 = 4 THEN jarak END) AS '4s',
       MAX(CASE WHEN idkota2 = 5 THEN jarak END) AS '5s'
FROM ms_jarak_kota
GROUP BY idkota1

Demo here