Unpivoting a Table

I have a pivot table with Routes going down and years going across, but I need to normalize it – have a column for the route and a column for the year and then a value for each route, column.

Route2015201620172018
North12141824
South23211917
East19241210
West45585759
Desert231031
Plains23232323
Scenic49483738
Bad Lands121288
Roundabout3336

Oracle and MS SQL databases have a function called UNPIVOT.

MySQL does not have a similar function yet, so you have to do a SELECT, UNION ALL type command.

In R Project, the function is called “Melt”

Doing this in R Project:

1. Install the reshape library if you don’t have it.
2. Program looks like so:


library(reshape)
rte_vol=read.csv("/users/sarod/documents/route_volume.csv")
rte_vol_melted<-melt(rte_vol, id=c("Route")) rte_vol_melted

Line 1 runs the reshape library
Line 2 reads the data in
Line 3 "Melts" the data
Line 4 Shows the output

MySQL Working with Nulls

Some useful MySQL commands:

How to find rows with null data and delete them:

delete from corridor where wb_id is null;

Note that this WON’T work:

delete from corridor where wb_id = null;

How to update a blank field: