Re: SELECTing multiple rows to a single output line

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 20 Aug 2005 22:39:10 -0700
Message-ID: <1124602750.614893.72380@g14g2000cwa.googlegroups.com>

If your concern is just running out of space on a line and you don't need the widget name repeated on each line and you are executing from SQL*Plus, then you can just add a space after each comma and use SET COLUMN ... FORMAT ... WORD_WRAPPED. I have provided an example of that below and below that I have provided exactly what you asked for, with the widget name repeated, with syntax that does not require SQL*Plus.

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            2 comp2
         3            3 comp3
         3            4 comp4

scott_at_ORA92> SELECT * FROM part
  2 /

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

           1 part1
           1 part2
           1 part3
           1 part4
           1 part5
           1 part6
           1 part7
           2 part3
           2 part5
           2 part7
           3 part2
           3 part4
           4 part2

13 rows selected.

scott_at_ORA92> -- query from SQL*Plus:
scott_at_ORA92> COLUMN part_names FORMAT A25 WORD_WRAPPED
scott_at_ORA92> SELECT widget,
  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (part_name, ', '), 3))
  3  	      AS part_names
  4  FROM   (SELECT widget_id, widget, 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,
 12  			    w.name AS widget,
 13  			    p.name AS part_name
 14  		     FROM   widget w, component c, part p
 15  		     WHERE  w.widget_id = c.widget_id
 16  		     AND    c.component_id = p.component_id
 17  		     AND    ROWNUM > 0))

 18 START WITH curr = 1
 19 CONNECT BY PRIOR curr = prev
 20 AND PRIOR widget_id = widget_id  21 GROUP BY widget_id, widget
 22 /

WIDGET PART_NAMES

------- -------------------------
widget1 part1, part2, part3,
        part4, part5, part6,
        part7

widget2 part3, part5, part7
widget3 part2, part4

scott_at_ORA92> CLEAR COLUMNS
scott_at_ORA92> COLUMN part_names FORMAT A40
scott_at_ORA92> -- query without SQL*Plus:
scott_at_ORA92> SELECT widget,
  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (part_name, ','), 2))
  3  	      AS part_names
  4  FROM   (SELECT widget_id, widget, part_name, threesome,
  5  		    ROW_NUMBER () OVER
  6  		      (PARTITION BY widget_id, threesome
  7  		       ORDER BY part_name) AS curr,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY widget_id, threesome
 10  		       ORDER BY part_name) - 1 AS prev
 11  	     FROM   (SELECT DISTINCT w.widget_id,
 12  			    w.name AS widget,
 13  			    p.name AS part_name,
 14  			    CEIL (DENSE_RANK () OVER
 15  				    (PARTITION BY w.widget_id
 16  				     ORDER BY p.name) / 3)
 17  			      AS threesome
 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 START WITH curr = 1
 22 CONNECT BY PRIOR curr = prev

 23  	    AND PRIOR widget_id = widget_id
 24  	    AND PRIOR threesome = threesome

 25 GROUP BY widget_id, widget, threesome  26 /

WIDGET PART_NAMES

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

widget1 part1,part2,part3
widget1 part4,part5,part6
widget1 part7
widget2 part3,part5,part7
widget3 part2,part4

scott_at_ORA92> Received on Sun Aug 21 2005 - 00:39:10 CDT