Re: SELECTing multiple rows to a single output line

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 15 Aug 2005 23:01:43 -0700
Message-ID: <1124172103.018298.221960@o13g2000cwo.googlegroups.com>

I don't have your table structure, so the following may not be exactly what you want, but it should be close enough for you to modify it to suit your needs. I haven broken the query down, one step at a time, starting from the innermost query, so that you can see how it is constructed. The query at the bottom is all you need. The rest are just to demonstrate the process.

scott_at_ORA92> -- test data:
scott_at_ORA92> SELECT * FROM widget
  2 /

 WIDGET_ID NAME
---------- -------

         1 widget1
         2 widget2
         3 widget3

scott_at_ORA92> SELECT * FROM component
  2 /

 WIDGET_ID COMPONENT_ID NAME
---------- ------------ -------

         1            1 comp1
         2            1 comp1
         2            2 comp2
         3            1 comp1
         3            1 comp2
         3            1 comp3

6 rows selected.

scott_at_ORA92> SELECT * FROM part
  2 /

COMPONENT_ID NAME
------------ -------

           1 part1
           1 part2
           2 part2
           2 part3
           3 part4
           3 part5

6 rows selected.

scott_at_ORA92> -- select with duplicates:
scott_at_ORA92> COLUMN part_name  FORMAT A25
scott_at_ORA92> COLUMN part_names FORMAT A25
scott_at_ORA92> SELECT w.widget_id, w.name,
  2  	    COUNT (*) OVER
  3  	      (PARTITION BY w.widget_id) AS parts,
  4  	    p.name || '-' ||
  5  	    COUNT (*) OVER
  6  	      (PARTITION BY w.widget_id, p.name)
  7  	      AS part_name

  8 FROM widget w, component c, part p   9 WHERE w.widget_id = c.widget_id
 10 AND c.component_id = p.component_id  11 /

 WIDGET_ID NAME PARTS PART_NAME
---------- ------- ---------- -------------------------

         1 widget1          2 part1-1
         1 widget1          2 part2-1
         2 widget2          4 part1-1
         2 widget2          4 part2-2
         2 widget2          4 part2-2
         2 widget2          4 part3-1
         3 widget3          6 part1-3
         3 widget3          6 part1-3
         3 widget3          6 part1-3
         3 widget3          6 part2-3
         3 widget3          6 part2-3
         3 widget3          6 part2-3

12 rows selected.

scott_at_ORA92> -- select without duplicates: scott_at_ORA92> SELECT DISTINCT w.widget_id, w.name,

  2  	    COUNT (*) OVER
  3  	      (PARTITION BY w.widget_id) AS parts,
  4  	    p.name || '-' ||
  5  	    COUNT (*) OVER
  6  	      (PARTITION BY w.widget_id, p.name)
  7  	      AS part_name

  8 FROM widget w, component c, part p   9 WHERE w.widget_id = c.widget_id
 10 AND c.component_id = p.component_id  11 /

 WIDGET_ID NAME PARTS PART_NAME
---------- ------- ---------- -------------------------

         1 widget1          2 part1-1
         1 widget1          2 part2-1
         2 widget2          4 part1-1
         2 widget2          4 part2-2
         2 widget2          4 part3-1
         3 widget3          6 part1-3
         3 widget3          6 part2-3

7 rows selected.

scott_at_ORA92> -- limit to less than 5 parts per widget: scott_at_ORA92> SELECT widget_id, widget, parts, part_name   2 FROM (SELECT DISTINCT w.widget_id, w.name AS widget,

  3  		    COUNT (*) OVER
  4  		      (PARTITION BY w.widget_id) AS parts,
  5  	     p.name || '-' ||
  6  	     COUNT (*) OVER
  7  	       (PARTITION BY w.widget_id, p.name)
  8  	       AS part_name
  9  	     FROM   widget w, component c, part p
 10  	     WHERE  w.widget_id = c.widget_id
 11  	     AND    c.component_id = p.component_id)

 12 WHERE parts < 5
 13 /

 WIDGET_ID WIDGET PARTS PART_NAME
---------- ------- ---------- -------------------------

         1 widget1          2 part1-1
         1 widget1          2 part2-1
         2 widget2          4 part1-1
         2 widget2          4 part2-2
         2 widget2          4 part3-1

scott_at_ORA92> -- add curr and prev:
scott_at_ORA92> SELECT widget_id, widget, parts, part_name,

  2  	    ROW_NUMBER () OVER
  3  	      (PARTITION BY widget_id
  4  	       ORDER BY part_name) AS curr,
  5  	    ROW_NUMBER () OVER
  6  	      (PARTITION BY widget_id
  7  	       ORDER BY part_name) - 1 AS prev
  8  FROM   (SELECT DISTINCT w.widget_id, w.name AS widget,
  9  		    COUNT (*) OVER
 10  		      (PARTITION BY w.widget_id) AS parts,
 11  		    p.name || '-' ||
 12  		    COUNT (*) OVER
 13  		      (PARTITION BY w.widget_id, p.name)
 14  		      AS part_name
 15  	     FROM   widget w, component c, part p
 16  	     WHERE  w.widget_id = c.widget_id
 17  	     AND    c.component_id = p.component_id)

 18 WHERE parts < 5
 19 /

 WIDGET_ID WIDGET       PARTS PART_NAME                       CURR

 PREV
---------- ------- ---------- ------------------------- ----------

---

         1 widget1          2 part1-1                            1
    0
         1 widget1          2 part2-1                            2
    1
         2 widget2          4 part1-1                            1
    0
         2 widget2          4 part2-2                            2
    1
         2 widget2          4 part3-1                            3

    2

scott_at_ORA92> -- add hierarchical query:
scott_at_ORA92> SELECT widget_id, widget, parts, part_name,   2 SYS_CONNECT_BY_PATH (part_name, ',') AS part_names   3 FROM (SELECT widget_id, widget, parts, part_name,

  4  		    ROW_NUMBER () OVER
  5  		      (PARTITION BY widget_id
  6  		       ORDER BY part_name) AS curr,
  7  		    ROW_NUMBER () OVER
  8  		      (PARTITION BY widget_id
  9  		       ORDER BY part_name) - 1 AS prev
 10  	     FROM   (SELECT DISTINCT w.widget_id, w.name AS widget,
 11  			    COUNT (*) OVER
 12  			      (PARTITION BY w.widget_id) AS parts,
 13  			    p.name || '-' ||
 14  			    COUNT (*) OVER
 15  			      (PARTITION BY w.widget_id, p.name)
 16  			      AS part_name
 17  		     FROM   widget w, component c, part p
 18  		     WHERE  w.widget_id = c.widget_id
 19  		     AND    c.component_id = p.component_id)
 20  	     WHERE  parts < 5)

 21 START WITH curr = 1
 22 CONNECT BY PRIOR curr = prev
 23 AND PRIOR widget_id = widget_id  24 /

 WIDGET_ID WIDGET       PARTS PART_NAME                 PART_NAMES


---------- ------- ---------- -------------------------

-------------------------

         1 widget1          2 part1-1                   ,part1-1
         1 widget1          2 part2-1
,part1-1,part2-1
         2 widget2          4 part1-1                   ,part1-1
         2 widget2          4 part2-2
,part1-1,part2-2
         2 widget2          4 part3-1

,part1-1,part2-2,part3-1

scott_at_ORA92> -- trim leading comma:
scott_at_ORA92> SELECT widget_id, widget, parts, part_name,   2 SUBSTR (SYS_CONNECT_BY_PATH (part_name, ','), 2) AS part_names
  3 FROM (SELECT widget_id, widget, parts, part_name,

  4  		    ROW_NUMBER () OVER
  5  		      (PARTITION BY widget_id
  6  		       ORDER BY part_name) AS curr,
  7  		    ROW_NUMBER () OVER
  8  		      (PARTITION BY widget_id
  9  		       ORDER BY part_name) - 1 AS prev
 10  	     FROM   (SELECT DISTINCT w.widget_id, w.name AS widget,
 11  			    COUNT (*) OVER
 12  			      (PARTITION BY w.widget_id) AS parts,
 13  			    p.name || '-' ||
 14  			    COUNT (*) OVER
 15  			      (PARTITION BY w.widget_id, p.name)
 16  			      AS part_name
 17  		     FROM   widget w, component c, part p
 18  		     WHERE  w.widget_id = c.widget_id
 19  		     AND    c.component_id = p.component_id)
 20  	     WHERE  parts < 5)

 21 START WITH curr = 1
 22 CONNECT BY PRIOR curr = prev
 23 AND PRIOR widget_id = widget_id  24 /

 WIDGET_ID WIDGET       PARTS PART_NAME                 PART_NAMES


---------- ------- ---------- -------------------------

-------------------------

         1 widget1          2 part1-1                   part1-1
         1 widget1          2 part2-1                   part1-1,part2-1
         2 widget2          4 part1-1                   part1-1
         2 widget2          4 part2-2                   part1-1,part2-2
         2 widget2          4 part3-1

part1-1,part2-2,part3-1

scott_at_ORA92> -- use max to get one full list of parts for each widget: scott_at_ORA92> SELECT widget_id, widget, parts,

  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (part_name, ','), 2))
  3  	      AS part_names
  4  FROM   (SELECT widget_id, widget, parts, part_name,
  5  		    ROW_NUMBER () OVER
  6  		      (PARTITION BY widget_id
  7  		       ORDER BY part_name) AS curr,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY widget_id
 10  		       ORDER BY part_name) - 1 AS prev
 11  	     FROM   (SELECT DISTINCT w.widget_id, w.name AS widget,
 12  			    COUNT (*) OVER
 13  			      (PARTITION BY w.widget_id) AS parts,
 14  			    p.name || '-' ||
 15  			    COUNT (*) OVER
 16  			      (PARTITION BY w.widget_id, p.name)
 17  			      AS part_name
 18  		     FROM   widget w, component c, part p
 19  		     WHERE  w.widget_id = c.widget_id
 20  		     AND    c.component_id = p.component_id)
 21  	     WHERE  parts < 5)

 22 START WITH curr = 1
 23 CONNECT BY PRIOR curr = prev
 24 AND PRIOR widget_id = widget_id  25 GROUP BY widget_id, widget, parts
 26 /

 WIDGET_ID WIDGET PARTS PART_NAMES
---------- ------- ---------- -------------------------

         1 widget1          2 part1-1,part2-1
         2 widget2          4 part1-1,part2-2,part3-1

scott_at_ORA92> Received on Tue Aug 16 2005 - 01:01:43 CDT