oracle correlated subquery using distinct listagg

Question

I have an interesting query I'm trying to figure out. I have a view which is getting a column added to it. This column is pivoted data coming from other tables, to form into a single row. Now, I need to wipe out duplicate entries in this pivoted data. Listagg is great for getting the data to a single row, but I need to make it unique. While I know how to make it unique, I'm tripping up on the fact that correlated sub-queries only go 1 level deep. So... not really sure how to get a distinct list of values. I can get it to work if I don't do the distinct just fine. Anyone out there able to work some SQL magic?

Sample data:

drop table test;
drop table test_widget;

create table test (id number, description Varchar2(20));

create table test_widget (widget_id number, test_fk number, widget_type varchar2(20));

insert into test values(1, 'cog');
insert into test values(2, 'wheel');
insert into test values(3, 'spring');

insert into test_widget values(1, 1, 'A');
insert into test_widget values(2, 1, 'A');
insert into test_widget values(3, 1, 'B');
insert into test_widget values(4, 1, 'A');
insert into test_widget values(5, 2, 'C');
insert into test_widget values(6, 2, 'C');
insert into test_widget values(7, 2, 'B');
insert into test_widget values(8, 3, 'A');
insert into test_widget values(9, 3, 'C');
insert into test_widget values(10, 3, 'B');
insert into test_widget values(11, 3, 'B');
insert into test_widget values(12, 3, 'A');

commit;

Here is an example of the query that works, but shows duplicate data:

SELECT A.ID
     , A.DESCRIPTION
     , (SELECT LISTAGG (WIDGET_TYPE, ', ') WITHIN GROUP (ORDER BY WIDGET_TYPE)
          FROM TEST_WIDGET
         WHERE TEST_FK = A.ID) widget_types
  FROM TEST A

Here is an example of what does NOT work due to the depth of where I try to reference the ID:

SELECT A.ID
     , A.DESCRIPTION
     , (SELECT LISTAGG (WIDGET_TYPE, ', ') WITHIN GROUP (ORDER BY WIDGET_TYPE)
          FROM (SELECT DISTINCT WIDGET_TYPE
                  FROM TEST_WIDGET
                 WHERE TEST_FK = A.ID))
          WIDGET_TYPES
  FROM TEST A

Here is what I want displayed:

1   cog A, B
2   wheel   B, C
3   spring  A, B, C

If anyone knows off the top of their head, that would fantastic! Otherwise, I can post up some sample create statements to help you with dummy data to figure out the query.

Answer 1

You can apply the distinct in a subquery, which also has the join - avoiding the level issue:

SELECT ID
     , DESCRIPTION
     , LISTAGG (WIDGET_TYPE, ', ')
         WITHIN GROUP (ORDER BY WIDGET_TYPE) AS widget_types
FROM (
  SELECT DISTINCT A.ID, A.DESCRIPTION, B.WIDGET_TYPE
  FROM TEST A
  JOIN TEST_WIDGET B
  ON B.TEST_FK = A.ID
)
GROUP BY ID, DESCRIPTION
ORDER BY ID;

        ID DESCRIPTION          WIDGET_TYPES       
---------- -------------------- --------------------
         1 cog                  A, B                
         2 wheel                B, C                
         3 spring               A, B, C