If value not in list, create rows with missing values

[Anonymous]

This needs a little bit of information.

I have a list of user IDs. I also have 20 modules that an user can be associated with, each module with a number of sessions that can be counted.

I currently have a table like this:

UserID	Module	Count
A	1	3
A	2	3
B	1	2
B	3	2
C	1	3
C	2	3
C	3	3

What am I trying to achieve is the following:

For every user ID, I need to check if there is one row per module, considering all 20 modules. The way the table is set up right now, when an user has nothing on a module, I don't have a row for the module they are not involved with. If there's a row missing, it should create the row for the missing module and set Count to 0.

So assuming the table above only considers 3 modules, I would need to transform it into:

UserID	Module	Count
A	1	3
A	2	3
A	3	0
B	1	2
B	2	0
B	3	2
C	1	3
C	2	3
C	3	3

How would I got about doing that? 

I'm getting my first row by grouping another table per module, where the count is the number of rows on the other table. However this doesn't deal with the missing modules, and I still need to report them as 0.

Would anyone be able to help me with that?

Thank you!

[v-yuta-msft · Accepted solution]

@Anonymous ,

You can create an external table with complete UserID and Module:

 

Then merge the two tables using left outer join and expand columns:

 

Finally, replace null with 0:

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Quartarolo]

@v-yuta-msft What if the list of users is not fixated? Its a dynamic list?