CODEWITHSUNDEEP
mysql
patricknz
patricknz

Reputation: 33

Pivot a MySQL database

I've got a table which has three columns, geoid1, geoid2 and distance.

For each record in geoid1 there are ten corresponding geoid2 records with the distance between 1 and 2.

I would like to make it a flat table with geoid1, and then the ten closest geoid2 records, by distance.

So at the moment it looks like this...

GeoID1	GeoID2	Distance
100	1444	13745.4
100	1446	26757.2
100	1410	39008.3
100	1383	49338.1
100	1455	63466.3
100	1467	70949.7
100	1462	72797.3
100	1374	76524.6
100	1389	79514.5
100	1372	84596.5
42300	1388	344.317
42300	1364	441.047
42300	1387	978.223
42300	1445	6116.52
42300	1443	8577.54
42300	1413	9398.04
42300	1412	9636.99
42300	1459	11861.8
42300	1435	14330.3
42300	1419	15440.5
51401	1415	1227.49
51401	1418	3111.48
51401	1370	3208.31
51401	1434	5350.56
51401	1394	8490.85
51401	1366	8727.67
51401	1395	8727.67
51401	1396	9093.37
51401	1417	12281.4
51401	1409	13033.9

And I want it looking like this...

GeoID1	ID2_1	ID2_2	ID2_3	ID2_4	ID2_5	ID2_6	ID2_7	ID2_8	ID2_9	ID2_10
100	1444	1446	1410	1383	1455	1467	1462	1374	1389	1372
42300	1388	1364	1387	1445	1443	1413	1412	1459	1435	1419
51401	1415	1418	1370	1434	1394	1366	1395	1396	1417	1409

Upvotes: 0

Views: 34

Answers (1)

FanoFN
FanoFN

Reputation: 7114

One way to achieve this is using a few operator like ROW_NUMBER, SUM and CASE. A query like this can get the exact output you wanted:

SELECT GeoID1, 
       SUM(CASE WHEN rnum=1 THEN GeoID2 END) AS 'ID2_1',
       SUM(CASE WHEN rnum=2 THEN GeoID2 END) AS 'ID2_2',
       SUM(CASE WHEN rnum=3 THEN GeoID2 END) AS 'ID2_3',
       SUM(CASE WHEN rnum=4 THEN GeoID2 END) AS 'ID2_4',
       SUM(CASE WHEN rnum=5 THEN GeoID2 END) AS 'ID2_5',
       SUM(CASE WHEN rnum=6 THEN GeoID2 END) AS 'ID2_6',
       SUM(CASE WHEN rnum=7 THEN GeoID2 END) AS 'ID2_7',
       SUM(CASE WHEN rnum=8 THEN GeoID2 END) AS 'ID2_8',
       SUM(CASE WHEN rnum=9 THEN GeoID2 END) AS 'ID2_9',
       SUM(CASE WHEN rnum=10 THEN GeoID2 END) AS 'ID2_10'
FROM
( SELECT ROW_NUMBER() OVER (PARTITION BY GeoID1 ORDER BY Distance) rnum,
         GeoID1, GeoID2 FROM test2 ) A 
GROUP BY GeoID1;

The first assignment is to give rownumber for each of the GeoID2 partitioned by GeoID1 and ordered by Distance. Then make that as a sub-query (or base query) and perform a SUM(CASE .. operation over it.

Check this fiddle out.

P/S: For reference, ROW_NUMBER() operator only can be used in MySQL 8++ and MariaDB 10.2++ versions; whether older versions will be getting an update with this functionality or not is yet to be known.. but it seems that its extremely unlikely that older versions will get this function.

Upvotes: 1

Related Questions