Counts from many tables into one set of results

I have multiple tables all with unique product IDs, a user ID and a date.

Is there any way to display a single table to show counts within each table split between each user ID.

I would want one row for each table name and multiple columns for each user id with counts for product IDs within the rows.

For instance:

TblName user1 user2 user3
tbl1      1     2     3
tbl2      4     5     6
tbl3      7     8     9
tbl4      3     2     1

I feel like this will require Pivot but I’m unsure on how to execute it. Up until this point I’ve been able to search and find everything I need to build a production database back-end and a functioning front-end user application but this one eludes me.

For some context the requested table is to be used to show production counts for employees across multiple tasks. If possible I’d like to have the date as a parameter for stored procedure so I can create a slider of sorts in the front-end user application.

I’ve read almost all the similar questions and they either seem to be not what I’m looking for or have gone unresolved or happen to be a request that can’t be done. I feel like mine may end up falling in the latter.

I also think I may just be terrible at knowing exactly what to search for to find the answer I’m looking for.

Please let me know if there is any additional information required to fulfill my request.

Answer

You can use conditional aggregation and union all

select 'table1' as tablename,
       sum(case when userid = 'user1' then 1 else 0 end) as user1,
       sum(case when userid = 'user2' then 1 else 0 end) as user2,
       sum(case when userid = 'user3' then 1 else 0 end) as user3
from table1
union all
select 'table2' as tablename,
       sum(case when userid = 'user1' then 1 else 0 end) as user1,
       sum(case when userid = 'user2' then 1 else 0 end) as user2,
       sum(case when userid = 'user3' then 1 else 0 end) as user3
from table2
union all
. . .;

I don’t see any advantage in trying to use pivot here. If the users are not known, however, you will need to use dynamic SQL.

Note: It is usually a sign of a bad data model that stores the same information across multiple tables. Typically, such information should all be in one table.