Merging and converting rows to columns

Question

I have a list of data that happens to have been set up like this:

entryID    fieldID    Value
100        1          John
100        2          Smith
100        3          USA
101        1          Jane
101        2          Doe
101        3          USA

The way it works is, when a "ticket" is created, it assigns an entryID for all the data in that ticket, but each field also has it's own fieldID. So first name is always a fieldID of 1, last name is 2, etc. What I need to be able to do is create a view that will look like this:

First Name    Last Name    Country
John          Smith        USA
Jane          Doe          USA

I need to be able to do this in MySQL, and not SQL or excel, as some other similar problems have addressed.

Is this data saved in a table or just as a list in a file somewhere? — Jeremy C., Apr 29 '15 at 14:07
This data is in a view table generated from multiple other tables. — user1853845, Apr 29 '15 at 15:17
possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Will B., Apr 29 '15 at 16:58

score 0 · Answer 1 · answered Apr 29 '15 at 16:27

0

I just found the answer to my own question, so I will post it here for future users. This website explains how to do exactly what I was asking for: http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

answered Apr 29 '15 at 16:27

user1853845

31
6

That is a lot of views to maintain for a simple pivot table that can be accomplished with `JOINS`. Imagine adding a new identifier as a column. You would have to update all three views. See my answer that you can create a view from. – Will B. Apr 29 '15 at 16:55

Merging and converting rows to columns

1 Answers1