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.
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;