Show related table field as field and list second related table fields as list

[Loek_van_Gent]

Our data models look a bit like this:

user: { id, email, name}
segment_type: {id, name}
segment: {id, segment_type_id, name}
user_segement: {id, segment_id, user_id}

We want to show a list of users with their ‘segments’

Table headers:
user id | user email | user name | segment_type.name #1 |  segment_type.name #2 | ….
So the number of columns is dependant on the number of segment_types we have.

Example  row
12 | ​​​​​user@example.xom | User Name | segment.name, segment.name | segment.name | …
Where segments are a comma separated list grouped by segment_type.

For now we can get something that looks like this by pivoting on segment_type, however this doesn’t work if we also want to show measures based on other tables, e.g. count of user actions. Those will then also be pivoted over different segment_types.