You will probably need to create a function to achieve that.
Something similar to this (this assumes a table with a ‘val’ field, which would be the column by which you want to group):
<pre>FUNCTION YourFunction (p_val IN NUMBER) RETURN VARCHAR2 IS
CURSOR YourCursor is
SELECT text FROM YourTable WHERE val = p_val;
FOR i IN YourCursor LOOP
l_result := l_result || i.text;
then you call the function within your query, this way:
<pre>SELECT val, YourFunction(val)
FROM (SELECT DISTINCT val FROM YourTable);</pre>
(kccrosser) I concur with Carlosdl on needing the function in general. I think you also need to consider how you are going to concatenate the data. Group By does not specify the order of the rows, so there is no guarantee as to what order the text will be retrieved and concatenated. Also, do you want to pack the row fields with some kind of separators (e.g., space, comma/space, vertical bar, or ???)?
However, if you are concerned with concatenating a FIXED number of rows (for each value), or a MAXIMUM number of rows, and there is some form of a “sequence counter” on the rows, then you can do that without a function by using a left join query, e.g.
Assuming the table has the “group by” columns “col1″ and “col2″, a sequence number column (“seqno”) containing 1 for the 1st row, 2 for the 2nd, etc., and the desired data is in column “text”, the following will return the concatenated data for up to 4 rows per group by set.
<pre>select t1.col1, t1.col2, t1.text || rtrim(‘ ‘ || t2.text) || rtrim(‘ ‘ || t3.text) || rtrim(‘ ‘ || t4.text)
left join mytable t2 on (t2.col1 = t1.col1 and t2.col2 = t1.col2 and t2.seqno = 2)
left join mytable t3 on (t3.col1 = t1.col1 and t3.col2 = t1.col2 and t3.seqno = 3)
left join mytable t4 on (t4.col1 = t1.col1 and t4.col2 = t1.col2 and t4.seqno = 4)
where t1.seqno = 1</pre>
You can also use the first_value and lead functions for this:
e.g. assuming text1 is the field you want to concatenate, and text2 and text3 are the other groups.
This example assumes you have 5 rows to concatenate.
select val.text2, val.text3, val.val1||val.val2||val.val3||val.val4||val.val5 as concat_val
(select text2, text3,
first_value (text1) over (partition by text2, text3 order by text2 , text3) val1,
lead (text1,1) over (partition by text2, text3 order by text2 , text3) val2,
lead (text1,2) over (partition by text2, text3 order by text2 , text3) val3,
lead (text1,3) over (partition by text2, text3 order by text2 , text3) val4,
lead(text1,4) over (partition by text2, text3 order by text2 , text3) val5,
row_number () over (partition by text2, text3 order by text2 , text3) limiter
from table1) val
where val.limiter = 1
Take care of putting in the limiter value, otherwise you will get repeats. Test the sql first without it, and you will be able to see what is being done, but use the limiter = 1 at the end. As you can see, the order by in the partitions derive the order that the values come in, so this can be controlled as well.