Combining Multiple Rows /Transposing each category ID to its own column

[webbj]

Hello all, I believe what I am attempting to do is combine multiple rows from my table. The core of what I want to do is combine all duplicated ID rows into a single row and have a seperate column per category id. 

The Issue: As each category is on a seperate row it is creating multiple seperate entries for each ID which means I cannot filter on multiple categories. When trying the graphs go blank.

What I need to be able to do: create a graph/report that can be filtered against multiple criteria i.e. a chart showing sentiment over time filterable by location.

RAW data view

Here is how the data initially appears via the API - I have removed some columns so I can post here. categories.path is the main category grouping and then categories.name is the subcategory.

Journey so far

I have seperated the table into Results and Categories, and created a relationship between them using the results.category.id in Results and available.categories.id in Categories. 

I created a quick excel spreadsheet to import to see if each category was a column rather than a row I could apply multiple filters, this seemed to work.

What I am trying to do

Create seperate tables for each category

Merge those tables into the results table to create the seperate columns using the existing results.category.id and the id in the new table. 

Remove the existing results.category.id column

Merge each row with the same ID so I end up with something similar to the excel spreadsheet demo.

ID, Date, Comments, channel.id, sentiment.id, etc 

Is there a better way to go about this scenario? 

How can I combine / flatten each id into a single row whilst keeping the value's present in the newly added columns?

I hope this makes sense, if any more information is required please let me know.