Tag: mysql

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.

Route	2015	2016	2017	2018
North	12	14	18	24
South	23	21	19	17
East	19	24	12	10
West	45	58	57	59
Desert	23	10	3	1
Plains	23	23	23	23
Scenic	49	48	37	38
Bad Lands	12	12	8	8
Roundabout	3	3	3	6

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