concatenating rows of a column in oracle 9i sql

15 pts.
Tags:
Concatenate
Oracle 9i
Oracle SQL
how can i concatenate several rows of a column which is of datatype varchar taking group by of two other columns in oracle 9i sql??

Answer Wiki

Thanks. We'll let you know when a new response is added.

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;
l_result VARCHAR2(500);
BEGIN
FOR i IN YourCursor LOOP
l_result := l_result || i.text;
END LOOP;
RETURN l_result;
END;</pre>

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)
from
mytable t1
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>

————————————————-
Hi,
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
from
(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.

Cheers,
Darryn
————————————————————–

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following