How to output unlimited value Field Collection with dates into a Views table format with no duplicates?

Question

I have a Content Type called "Safety Training". It has various fields within it, including a Field Collection named "Dates & Locations", set to unlimited values so they can add infinite date/location pairs.

The two fields defined within the Field Collection are Date (a date ISO field with the ability to enter start/end date and times) and Location (taxonomy). The Date and Location fields are both only single value.

I have a Views page, where I am outputting the individual nodes and grouping them by category of Course Type. This is the format I need the Views table in (from the old site, static HTML):

This is what I have right now in Views (this is in the preview):

How can I separate the date, time, and location into individual table columns called "Class Date", "Location", and "Start/End Time" that will group them like in the old site?

I tried creating a Relationship, choosing "Content: Dates & Locations (field_safety_date_location)", but this immediately creates a duplicate of each item (so there's two Worker Initials, because there's two dates?).

This is the SQL query Views displays:

SELECT node.title AS node_title, node.nid AS nid, draggableviews_structure.weight AS draggableviews_structure_weight, 'node' AS field_data_field_safety_date_location_node_entity_type, 'node' AS field_data_field_safety_standard_fee_node_entity_type, 'node' AS field_data_field_safety_late_fee_node_entity_type, 'node' AS field_data_field_registration_link_node_entity_type, 'node' AS field_data_field_safety_category_node_entity_type
FROM 
{node} node
LEFT JOIN {draggableviews_structure} draggableviews_structure ON node.nid = draggableviews_structure.entity_id AND draggableviews_structure.view_name = 'safety_environmental_training_schedule_and_fees' AND draggableviews_structure.view_display = 'page_1' AND draggableviews_structure.args = '[]'
WHERE (( (node.status = '1') AND (node.type IN  ('safety_environmental_training')) ))
ORDER BY draggableviews_structure_weight ASC

If anyone has any idea, please help! Thank you.

Answer 1

Revisiting this two years later after asking if I'd solved it. In short, yes, I did. :)

Here's a screenshot of the final product, still live:

Here's a screenshot of the view as of this morning:

Basically, I created two fields of the Field Collection item: Date, which I was able to access by creating a relationship to the Field Collection. I then changed the formatting of those Field Collections to be what I wanted them to be.

In the Table's settings, I used Grouping field Nr.1 to group them by Content: Category, using the rendered output to group rows. I also enabled sticky table headers so that the headers will follow the table down.

For the class date, I set the formatter to Date Range, and set the format to "F j" for single date, single date all day, single day date range. For Date format for single month date range, I set it to F j – {j}, for single year date range I set it to F j – {F} {j}. For multiple years date range, I set it to F j, Y – {F} {j}, Y

No aggregation or distinct functionality was used in the view, as all of the trainings can only be part of a single category, so I didn't get duplicates. You may need to factor that in. I've always had great results from the Views Distinct module when running into that problem.

The items are sorted first by the taxonomy term weight, and then by item. I also integrated DraggableViews so the editor can re-arrange them as they saw fit rather than alphabetically if they want. (I actually should remove the sort by start date bit, as that doesn't do anything.)

Hope that helps.