Posted on

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

Posted on

Removing Spaces from a Field

How to remove a spaces from data within a field, either from the sides or from the middle

Case 1

The spaces are on either side of the data and any spaces in the middle should stay.
Example: ” Go To ” becomes “Go To”

Access QBE

In an update query, use this expression in “Update To”

Trim ([field1])

Case 2

The space are in the middle of the data and should be removed
Example: ” Go To ” becomes “GoTo”

MS SQL

use [database]
update [table “” not found /]

set [field1] = replace([field1],’ ‘,”)
go

Note that you must use the single quote. Also there is a space between the first pair of single quotes and there is nothing between the second pair of single quotes. So you are basically telling the system to replace a space with nothing.

Access QBE

In an update query, use this expression in “Update To”

Replace ([field1],” “,””)

Excel

=SUBSTITUTE([cell];” “;””)

Posted on

Using CASE in MS SQL

Code to update a field using “CASE” (instead of using if-then-else):

Posted on

Delete all rows in a table

SQL statement for MS SQL to delete all the rows in a table: