Varying Rows Converted to Columns

[mskusace]

Good Morning,

I have a report I pull that has a basic data structure of the table below. I have masked and simplified the data.

ID	Sub_ID	User	Role
1	A	Person1	Supervisor
1	A	Person2	Analyst
1	B	Person3	Assistant
2	C	Person4	Supervisor
2	C	Person5	Analyst
2	C	Person6	Analyst
2	C	Person7	Analyst
2	C	Person8	Intern

<tbody>
</tbody>

I combine the "ID" and "Sub_ID" to create a composite key for the current processing.
Notice that there are multiple entries for ID+Sub_ID because there are multiple roles for that combination of ID+Sub_ID.
What I do for the data processing is to make the ID+Sub_ID a unique column. Then I was planning on turning the roles into different columns. The problem that I run into is that it is not consistent.
For example, 1A (ID+Sub_ID) has 2 roles, 1B has 1 role, 2C has 5 roles, and 3 of them are the same, but they still need to be in separate columns. I need to be able to view each role for each ID+Sub_ID after the data is processed.

Then I thought about feeding this processed data into Qlik Sense, a visualization tool, and make the data more of a database structure with all 4 of those columns as dimension tables and then one fact table using all those foreign keys from the dimension tables. Ultimately, a user could then just click into their combination of ID+Sub_ID and see the varying amounts of "Roles". I am not sure what the best method was and I guess this is more of a data management question and how should I proceed?

I apologize if this is in the wrong section.

Please let me know if you need any more clarification.

 

[mskusace]

I apologize for bumping my thread, but I could not figure out how to edit my original post. I am just posting some clarification. The potential output I was thinking would be the following then:

ID + Sub_ID	Supervisor	Analyst	Analyst	Analyst	Assistant	Intern
1A	Person1	Person2
1B					Person3
2C	Person4	Person5	Person6	Person7		Person8

<tbody>
</tbody>

However, my question still stands on the best way to manage and process the data.