shutUpAndCode shutUpAndCode - 3 months ago 10
MySQL Question

Can I create a mapping from interger values in a column to the text values they represent in sql?

I have a table full of traffic accident data with column headers such as 'Vehicle_Manoeuvre' which contains integers for example 13 represents the vehicle manoeuvre which caused the accident was 'overtaking moving vehicle'.

I know the mappings from integers to text as I have a (quite large) excel file with this data.

An example of what I want to know is percentage of the accidents involved this type of manoeuvre but I don't want to have to open the excel file and find the mappings of integers to text every time I write a query.

I could manually change the integers of all the columns (write query with all the possible mappings of each column, add them as new column, then delete the orginial columns) but this sould take a long time.

Is it possible to create some type of variable (like an array with first column as integers and second column with the mapped text) that SQL could use to understand how text relates to the integers allowing me to write a query below:

SELECT COUNT(Vehicle_Manoeuvre) FROM traffictable WHERE Vehicle_Manoeuvre='overtaking moving vehicle';


rather than:

SELECT COUNT(Vehicle_Manoeuvre) FROM traffictable WHERE Vehicle_Manoeuvre=13;


even though the data in the table is still in integer form?

Answer

You would do this with a Maneeuvres reference table:

create table Manoeuvres (
    ManoeuvreId int primary key,
    Name varchar(255) unique
);

insert into Manoeuvres(ManoeuvreId, Name)
    values (13, 'Overtaking');

You might even have such a table already, if you know that 13 has a special meaning.

Then use a join:

SELECT COUNT(*)
FROM traffictable tt JOIN
     Manoeuvres m
     ON tt.Vehicle_Manoeuvre = m.ManoeuvreId
WHERE m.name = 'Overtaking';