Concat rows

[ekta22]

Hi All,

In my table every comment that a user enters for a particular ID is stored as a new record along with the sequence number. For ex.

ID SequenceNo Comment
1-----1--------Comment1
1-----2--------Comment2
1-----3--------Comment3
1-----4--------Comment4
2-----1--------Comment1
2-----2--------Comment2

Now I want to combine all the 4 comments for that record ID into one field. But I don't want to combine all the comments in one row. They should be in one field but not displayed as one row but on a new line. The result should something like this

ID Comment
1---Comment1
----Comment2
----Comment3
----Comment4
2---Comment1
----Comment2

How to do this?

Thanks
-E

 

[jimirvine]

Look for the SQLPLUS command 'BREAK'

 

[Turkbear]

Hi,
If you truly combine them into 1 field do you want that combined information to be in every record that has the same ID?
Like this?
1---Comment1,----Comment2,----Comment3,----Comment4
1---Comment1,----Comment2,----Comment3,----Comment4
1---Comment1,----Comment2,----Comment3,----Comment4
1---Comment1,----Comment2,----Comment3,----Comment4

Show the desired result after your combining step, please.Not just how it is displayed ( use the BREAK SqlPlus option as jimirvine mentioned to do that with no data alteration needed) but what would be actually stored in the record(s)...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ekta22]

Actually, we are trying to move all comments in one field in our new table. I know comments should be stored in a different table but don't ask me why we are doing it. What I want to do is somehow combine all comments in one field for that record. But when combining I want to add some kind of a line break so that when the information is displayed on web the comments appear on seperate lines.
So basically it will look like this in the table

1---Comment1,----Comment2,----Comment3,----Comment4
2---Comment1,----Comment2

On Web
Record ID - 1
Comments
Comment1,
Comment2,
Comment3,
Comment4

Record ID - 2
Comments
Comment1,
Comment2

 

[Turkbear]

Hi,
Maybe this:

By using Pl/Sql and looping through a cursor
by ID and Sequence # ( for each ID, do a select max(Sequence#) from the table where the ID matches) to get the # of comments and use this for the inner loop), you should be able to concatenate into a string the info you want ( use the CHR() function to add a lne feed)..after each ID, insert the string you created into the field you want to populate..




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ekta22]

Thanks, will try it out.

 

[SantaMufasa]

Ekta,

I'm rather tied up on a paying assignment presently (else I would build a sample resolution from your sample data), but if you search in these Oracle fora for my "matrix" function (that strings horizontally the vertical data from sibling rows), it should provide you with the solution components you need, pre-built, pre-tested.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Will do. Thanks Mufasa!