Trying to compare several similiar lists on one table to produce a concatenated result So imagine that four friends are planning on moving into a house together.  They put together a list of stuff that they all own.  Obviously, there is some overlap but there is also unique things among them.

Table LIST_OF_STUFF is a fairly simple table:

LIST_ID   ITEM
47        Baseball
47        Hat
47        Gloves
47        Sparkle Shoes
91        Baseball
91        Sparkle Shoes
91        Bubble Gum
91        Hat
91        Fish Tank
91        Book of Spiders
103       Silver Spoon
103       Fiddle
103       Trumpet
103       Paper Towels
103       Fireworks
103       Mosquito Netting
103       Cure for the Common Cold
295       Baseball
295       Fireworks
295       Trumpet
295       Bubble Gum
295       Hat
295       Gloves
295       Christmas Tree


This table contains lists unrelated to these four people (e.g. shopping lists, my favorite vacation spots, etc.) but the specific LIST_IDs I'm interested in are shown above.  I want to be able to make results similar to the following:

LIST_47          LIST_91          LIST_103...
Baseball         Baseball         (null)
Hat              Hat              (null)
(null)           Fish Tank        (null)
(null)           (null)           Silver Spoon


I initially thought a FULL OUTER JOIN on LIST_OF_STUFF would do the trick but it didn't return all the values on the other lists.  It would only be compared to LIST_47:

SELECT LIST_47.ITEM LIST_47_LIST
     , LIST_91.ITEM LIST_91_LIST
     , LIST_103.ITEM LIST_103_LIST
     ...
FROM LIST_OF_STUFF LIST_47
FULL OUTER JOIN LIST_OF_STUFF LIST_91.ITEM = LIST_47.ITEM AND LIST_91.LIST_ID = 91
FULL OUTER JOIN LIST_OF_STUFF LIST_103.ITEM = LIST_47.ITEM AND LIST_103.LIST_ID = 103
...
WHERE LIST_47.LIST_ID = 47


What is the best method to return such information?
JOIN is not that effective, because you need to access the table 4 times. With PIVOT you can do it with 1 table access.

create table list_of_stuff 
(
  list_id number(5,0),
  item varchar2(40 char)
);

insert all
  into list_of_stuff values (47, 'Baseball')
  into list_of_stuff values (47, 'Hat')
  into list_of_stuff values (47, 'Gloves')
  into list_of_stuff values (47, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Baseball')
  into list_of_stuff values (91, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Bubble Gum')
  into list_of_stuff values (91, 'Hat')
  into list_of_stuff values (91, 'Fish Tank')
  into list_of_stuff values (91, 'Book of Spiders')
  into list_of_stuff values (103, 'Silver Spoon')
  into list_of_stuff values (103, 'Fiddle')
  into list_of_stuff values (103, 'Trumpet')
  into list_of_stuff values (103, 'Paper Towels')
  into list_of_stuff values (103, 'Fireworks')
  into list_of_stuff values (103, 'Mosquito Netting')
  into list_of_stuff values (103, 'Cure for the Common Cold')
  into list_of_stuff values (295, 'Baseball')
  into list_of_stuff values (295, 'Fireworks')
  into list_of_stuff values (295, 'Trumpet')
  into list_of_stuff values (295, 'Bubble Gum')
  into list_of_stuff values (295, 'Hat')
  into list_of_stuff values (295, 'Gloves')
  into list_of_stuff values (295, 'Christmas Tree')
select * from dual;

commit;


JOIN version:

SQL&gt; set null "(null)"
SQL&gt; r
  1  select *
  2  from
  3                  (select * from list_of_stuff where list_id = 47)  L1
  4  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  5  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
  6  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
  7  order by item
  8*

ITEM                              LIST_ID    LIST_ID    LIST_ID    LIST_ID
------------------------------ ---------- ---------- ---------- ----------
Baseball                               47         91 (null)            295
Book of Spiders                (null)             91 (null)     (null)
Bubble Gum                     (null)             91 (null)            295
Christmas Tree                 (null)     (null)     (null)            295
Cure for the Common Cold       (null)     (null)            103 (null)
Fiddle                         (null)     (null)            103 (null)
Fireworks                      (null)     (null)            103        295
Fish Tank                      (null)             91 (null)     (null)
Gloves                                 47 (null)     (null)            295
Hat                                    47         91 (null)            295
Mosquito Netting               (null)     (null)            103 (null)
Paper Towels                   (null)     (null)            103 (null)
Silver Spoon                   (null)     (null)            103 (null)
Sparkle Shoes                          47         91 (null)     (null)
Trumpet                        (null)     (null)            103        295


This returns a NULL where the item is not on the list, otherwise it returns the list_id. The actual value does not matter, just the fact that it is not null, so providing the desired output:

SQL&gt; r
  1  select
  2    nvl2(L1.list_id, item, null) as LIST_47,
  3    nvl2(L2.list_id, item, null) as LIST_91,
  4    nvl2(L3.list_id, item, null) as LIST_103,
  5    nvl2(L4.list_id, item, null) as LIST_295
  6  from
  7                  (select * from list_of_stuff where list_id = 47)  L1
  8  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  9  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
 10  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
 11  order by item
 12*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet


This requires 28 buffer gets on my demo table:

SQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'iostats last'));
...

Plan hash value: 912304261

------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |     15 |00:00:00.01 |      28 |
|   1 |  SORT ORDER BY             |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   2 |   VIEW                     | VW_FOJ_0      |      1 |     21 |     15 |00:00:00.01 |      28 |
|*  3 |    HASH JOIN FULL OUTER    |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   4 |     VIEW                   |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|*  5 |      TABLE ACCESS FULL     | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
|   6 |     VIEW                   | VW_FOJ_1      |      1 |     13 |     14 |00:00:00.01 |      21 |
|*  7 |      HASH JOIN FULL OUTER  |               |      1 |     13 |     14 |00:00:00.01 |      21 |
|   8 |       VIEW                 | VW_FOJ_2      |      1 |      6 |      7 |00:00:00.01 |      14 |
|*  9 |        HASH JOIN FULL OUTER|               |      1 |      6 |      7 |00:00:00.01 |      14 |
|  10 |         VIEW               |               |      1 |      4 |      4 |00:00:00.01 |       7 |
|* 11 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      4 |      4 |00:00:00.01 |       7 |
|  12 |         VIEW               |               |      1 |      6 |      6 |00:00:00.01 |       7 |
|* 13 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      6 |      6 |00:00:00.01 |       7 |
|  14 |       VIEW                 |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|* 15 |        TABLE ACCESS FULL   | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------


Then I have this PIVOT query that puts the number 1 (or I could have written any other value in MIN(), or use another aggregate function) to a list column where the item in the row exists:

SQL&gt; set null "(null)"
SQL&gt; r
  1  select *
  2  from
  3  (
  4    select list_id, item
  5    from   list_of_stuff
  6    where  list_id in (47, 91, 103, 295)
  7  )
  8  pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
  9  order by item
 10*

ITEM                           47_IN_LIST 91_IN_LIST 103_IN_LIST 295_IN_LIST
------------------------------ ---------- ---------- ----------- -----------
Baseball                                1          1 (null)                1
Book of Spiders                (null)              1 (null)      (null)
Bubble Gum                     (null)              1 (null)                1
Christmas Tree                 (null)     (null)     (null)                1
Cure for the Common Cold       (null)     (null)               1 (null)
Fiddle                         (null)     (null)               1 (null)
Fireworks                      (null)     (null)               1           1
Fish Tank                      (null)              1 (null)      (null)
Gloves                                  1 (null)     (null)                1
Hat                                     1          1 (null)                1
Mosquito Netting               (null)     (null)               1 (null)
Paper Towels                   (null)     (null)               1 (null)
Silver Spoon                   (null)     (null)               1 (null)
Sparkle Shoes                           1          1 (null)      (null)
Trumpet                        (null)     (null)               1           1


Similarly, after transforming it to the desired output:

SQL&gt; r
  1  select
  2    nvl2("47_IN_LIST", item, null)  as list_47,
  3    nvl2("91_IN_LIST", item, null)  as list_91,
  4    nvl2("103_IN_LIST", item, null) as list_103,
  5    nvl2("295_IN_LIST", item, null) as list_295
  6  from
  7  (
  8    select *
  9    from
 10    (
 11      select list_id, item
 12      from   list_of_stuff
 13      where  list_id in (47, 91, 103, 295)
 14    )
 15    pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
 16  )
 17  order by item
 18*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet


Much simpler, 1 table access, 7 buffer gets:

SQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'iostats last'));

...

Plan hash value: 1664708432

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     15 |00:00:00.01 |       7 |
|   1 |  SORT GROUP BY PIVOT|               |      1 |     24 |     15 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL | LIST_OF_STUFF |      1 |     24 |     24 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("LIST_ID"=47 OR "LIST_ID"=91 OR "LIST_ID"=103 OR "LIST_ID"=295))


Now if you don't insist on naming the columns LIST_47, LIST_91, ..., the JOIN version peforms worse, but it can be easily reused, by changing the 4 list_id parameters, you can even use bind variables there. The PIVOT performs better, but if you want to use different list_id values, you need to rewrite the query, because you can not dynamically provide the values for the pivot( ... in (***)). PIVOT needs to know the column names it generates at parse time.