Re: SELECTing multiple rows to a single output line

"Barbara Boehmer" <baboehme_at_hotmail.com> wrote:
>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>

Even though you misunderstood my meaning of the poorly stated "where the part count is under some number" phrase, I was able to figure out from your last solution example how to solve my problem. The key was that inside SELECT; that's where the DISTINCT qualifier had to be placed. My main hangup here is that I never had to resort to such nested SELECTs before and seldom had to use even any aggregate functions. The only way I used MAX() before was to find the largest number in a column. Frankly I still don't get how that function gets into this picture but it seems to work. ;-) That ROW_NUMBER () OVER (PARTITION ...) in combination with that new SYS_CONNECT_BY_PATH () function seems to be the key to this solution and they were both new to me as well. It's all pretty amazing really, and quite elegant. So thanks again for the tremendous help, Barbara.

Rudy Received on Tue Aug 16 2005 - 21:53:49 CDT