I have MySQL table similar to below table
| id | Source | Destination |
|---|---|---|
| 1 | BLR | ATH |
| 2 | ATH | BLR |
| 3 | BLR | HKG |
| 4 | HKG | BLR |
| 5 | DEL | ATH |
| 6 | ATH | DEL |
| 7 | DEL | HKG |
| 8 | HKG | DEL |
| 9 | BLR | DEL |
| 10 | DEL | BLR |
| 11 | BLR | ATH |
| 12 | ATH | BLR |
| 13 | DEL | ATH |
| 14 | ATH | DEL |
I want to generate the combination of source and destination based on unique city, along with count like
| CITY | ATH | BLR | DEL | HKG |
|---|---|---|---|---|
| ATH | 0 | 2 | 2 | 0 |
| BLR | 2 | 0 | 1 | 1 |
| DEL | 2 | 1 | 0 | 1 |
| HKT | 0 | 1 | 1 | 0 |
It is possible to generate the complete table using SQL query ? Thanks.
I'm not sure how this combination is called in SQL. All the searches returned table to generate unique combination of source/destination but not a table like this. Not sure what this kind of table where row title and column title are same, is called.
CSV for quick reference:
id,source,destination
1,BLR,ATH
2,ATH,BLR
3,BLR,HKG
4,HKG,BLR
5,DEL,ATH
6,ATH,DEL
7,DEL,HKG
8,HKG,DEL
9,BLR,DEL
10,DEL,BLR
11,BLR,ATH
12,ATH,BLR
13,DEL,ATH
14,ATH,DEL