| Concatenating multi line data [message #371269] |
Mon, 25 September 2000 23:44  |
Matt
Messages: 43
Registered: September 1999
|
Member |
|
|
Hello.
I download masses of property ownership data from a mainframe each night, and create tables in a similar form to those that are stored on said mainframe. For example :
Prop_Num Owner Address
11234 Husband 123 First Street
11234 Wife 123 First Street
I would like to create a table or view with the data looking a little like this :
Prop_Num Owners Address
11234 Husband and Wife 123 First Street
Does anyone have any ideas on doing this.
Thanks in advance ....
Matt
|
|
|
|
| Re: Concatenating multi line data [message #371280 is a reply to message #371269] |
Wed, 27 September 2000 07:52  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
you can try this update statments.
update t1 set owners = (select a.owners || ' and ' || b.owners
from t1 a, t1 b
where a.prop_num = b.prop_num
and a.rowid > b.rowid)
/
select * from t1;
prop_num owners address
11234 husband and wife 123 first street
11234 husband and wife 123 first street
now you can delete the duplicate records.
Good Luck
Bala.
|
|
|
|
| Re: Concatenating multi line data [message #371281 is a reply to message #371269] |
Wed, 27 September 2000 07:52 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
you can try this update statments.
update t1 set owners = (select a.owners || ' and ' || b.owners
from t1 a, t1 b
where a.prop_num = b.prop_num
and a.rowid > b.rowid)
/
select * from t1;
prop_num owners address
11234 husband and wife 123 first street
11234 husband and wife 123 first street
now you can delete the duplicate records.
Good Luck
Bala.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
