Individuals in multiple departments affecting grand total count

Question

I have a report I am trying to simplify but I am running into an issue.

(Undesired) The rows/columns of the report currently look like the following.

Department	Total	Probation (%)	Suspended (%)
All Employees	32	16.3	1.4
All Teams	30	23.5	2.2
Total Men's Teams	10	14.8	2.8
Total Women's Teams	10	34.3	1.4
Men's Wear	10	5.9	0.0
Women's Wear	10	21.4	0.0
UniSec Wear	10	15.0	6.3

This is happening because two people work on two teams. One person works in Mens Wear and UniSex Wear, and one person works in Women's Wear and UniSex Wear. The below table has records like this.

Col1	Col2
1234	Men's Wear
1234	UniSex Wear
9876	Women's Wear
9876	UniSex Wear

(Desired) Im looking for something like this.

Department	Total	Probation (%)	Suspended (%)
All Employees	30	16.3	1.4
All Teams	30	23.5	2.2
Total Men's Teams	10	14.8	2.8
Total Women's Teams	10	34.3	1.4
Men's Wear	10	5.9	0.0
Women's Wear	10	21.4	0.0
UniSec Wear	10	15.0	6.3

I have thought about using LISTAGG() on Col2 to get this affect.

Col1	Col2
1234	Men's Wear,UniSex Wear
9876	Women's Wear,UniSex Wear

Using LISTAGG() gives me the correct count for "All Employees" but then I get groupings of "Men's Wear,UniSex Wear" instead of a separate one for "Men's Wear" and one for "UniSex Wear". Is it possible to group by the individual comma separated values in Col2 after they have been LISTAGG()'ed, or is there a better way of achieving my end results?

Any assistance on achieving this would be greatly appreciated.

Answer 1

I would advise correcting the All_Employees data alone instead of doing the LISTAGG. OR Use a separate table to LISTAGG and un-LISTAGG your data which is different from the original table used to calculate the Total, Probation and Suspended data

For un-LISTAGG you can use the below example where table_two is your source table.

    with  
    d2 as (
      select 
        distinct id, 
        regexp_substr(
          products, '[^,]+', 1, column_value
        ) as products 
      from 
        table_two cross 
        join TABLE(
          Cast(
            MULTISET (
              SELECT 
                LEVEL 
              FROM 
                dual CONNECT BY level <= REGEXP_COUNT(products, '[^,]+')
            ) AS sys.ODCINUMBERLIST
          )
        )
    ) 
    SELECT 
      ID, 
      PRODUCTS 
    FROM 
      d2;