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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Sub_ID[/TD]
[TD]User[/TD]
[TD]Role[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]Person1[/TD]
[TD]Supervisor[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]Person2[/TD]
[TD]Analyst[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]Person3[/TD]
[TD]Assistant [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]Person4[/TD]
[TD]Supervisor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]Person5[/TD]
[TD]Analyst[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]Person6[/TD]
[TD]Analyst[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]Person7[/TD]
[TD]Analyst[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]Person8[/TD]
[TD]Intern[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID + Sub_ID[/TD]
[TD]Supervisor[/TD]
[TD]Analyst[/TD]
[TD]Analyst[/TD]
[TD]Analyst[/TD]
[TD]Assistant[/TD]
[TD]Intern[/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD]Person1[/TD]
[TD]Person2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2C[/TD]
[TD]Person4[/TD]
[TD]Person5[/TD]
[TD]Person6[/TD]
[TD]Person7[/TD]
[TD][/TD]
[TD]Person8[/TD]
[/TR]
</tbody>[/TABLE]

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