unshetty
asked on
Concatenate multiple rows into one row - Oracle
Hi:
I want to concatenate multiple rows into one row. Solution should work for Oracle.
e.g. If the data rows are -
a 1
a 2
a 3
b 1
b 4
result should be
a 123
b 14
This is urgent. Quicker But not Perfect answer is better than Perfect but not Quicker Answer.
Regards,
UNShetty
I want to concatenate multiple rows into one row. Solution should work for Oracle.
e.g. If the data rows are -
a 1
a 2
a 3
b 1
b 4
result should be
a 123
b 14
This is urgent. Quicker But not Perfect answer is better than Perfect but not Quicker Answer.
Regards,
UNShetty
write a PL/sql script
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Interesting . ...
Will watch for a while, before I accept the answer.
I am afraid, I am not at liberty to create a new function or temporary table of any sort.
Will watch for a while, before I accept the answer.
I am afraid, I am not at liberty to create a new function or temporary table of any sort.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this homework?
I refer to question:
https://www.experts-exchange.com/questions/21429675/Split-a-row-into-multiple-rows-Oracle.html
Which is the EXACT opposite of this? Asking how to convert 'a' to 'b' then convert 'b' back to 'a', is just a bit fishy?
I refer to question:
https://www.experts-exchange.com/questions/21429675/Split-a-row-into-multiple-rows-Oracle.html
Which is the EXACT opposite of this? Asking how to convert 'a' to 'b' then convert 'b' back to 'a', is just a bit fishy?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DrSQL:
- Order is not an issue. Concatenation in any order is fine with me.
- But thanks for that input
- I dont think I can use your query solution, as I may not know (or it could be very large number) how many records will be there for a given value in column1.
Slighwv:
- No. This is not a home work. Actually I repeated same questions for FoxPro also.
- I have to get data from Oracle and FoxPro tables and process them where I need to put them in one way (concatenated values) in some places and multiple rows in another place.
Outin:
- Yours is the best I got I think ( for my situation).
- Now we are executing this query through a Java program using JDBC to Oracle 9i
- Is there any clause in SQL solution, I can not use in this setup ? please let me know.
catchmeifuwant & geotiger:
- I will also try your solutions. Thanks you.
For all:
I will award the points soon.
Regards,
UNShetty
- Order is not an issue. Concatenation in any order is fine with me.
- But thanks for that input
- I dont think I can use your query solution, as I may not know (or it could be very large number) how many records will be there for a given value in column1.
Slighwv:
- No. This is not a home work. Actually I repeated same questions for FoxPro also.
- I have to get data from Oracle and FoxPro tables and process them where I need to put them in one way (concatenated values) in some places and multiple rows in another place.
Outin:
- Yours is the best I got I think ( for my situation).
- Now we are executing this query through a Java program using JDBC to Oracle 9i
- Is there any clause in SQL solution, I can not use in this setup ? please let me know.
catchmeifuwant & geotiger:
- I will also try your solutions. Thanks you.
For all:
I will award the points soon.
Regards,
UNShetty
UNShetty,
Just to clarify. The looper is not for the number of occurances of column one. It is simply the character position for the concatenated string. You should know how big that will be. You just need an entry in looper for every character that you might have to pull in. If you don't care about the order just use ROWNUM in place of charorder. Of course, if the concatenated string could be more than 10 or so chracters I think I'd prefer Outin's solution. You might also want to check Oracle Magazine's site. There was an article a few years ago where they did something similar (but a a little more elegant - no offense outin) using GROUPING. I did't post it because Outin had already started down that path.
Good luck!
Dr*SQL
Just to clarify. The looper is not for the number of occurances of column one. It is simply the character position for the concatenated string. You should know how big that will be. You just need an entry in looper for every character that you might have to pull in. If you don't care about the order just use ROWNUM in place of charorder. Of course, if the concatenated string could be more than 10 or so chracters I think I'd prefer Outin's solution. You might also want to check Oracle Magazine's site. There was an article a few years ago where they did something similar (but a a little more elegant - no offense outin) using GROUPING. I did't post it because Outin had already started down that path.
Good luck!
Dr*SQL
ASKER
Dr*SQL:
- can you post that solution [Oracle Magazine's site article little more elegant - using GROUPING] URL, if you have it. Older the solution better for me. Thanks
- can you post that solution [Oracle Magazine's site article little more elegant - using GROUPING] URL, if you have it. Older the solution better for me. Thanks
UnShetty,
Here's the link: http://asktom.oracle.com/pls/ask/f?p=4950:8:7900625568151064789::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402
It's part of OTN, so you'll have to register - but it is free. Having re-read the solution, it doesn't meet your requirement that there not be any database changes. But, it's still pretty slick.
Good luck!
DrSQL
P.S. I like Outin's solution with your restrictions.
Here's the link: http://asktom.oracle.com/pls/ask/f?p=4950:8:7900625568151064789::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402
It's part of OTN, so you'll have to register - but it is free. Having re-read the solution, it doesn't meet your requirement that there not be any database changes. But, it's still pretty slick.
Good luck!
DrSQL
P.S. I like Outin's solution with your restrictions.
ASKER
Thanks DrSQL. I am using it. I am posting another question related to Oracel with a text file in 5 minutes. If time permits can you have a look at it ? It will be titled, "How to join a text file with a Oracel table - without any database changes".
Use an aggregate pl/sql function. Search on AskTom and stragg !
earthman2,
That's what my link was pointing to.
That's what my link was pointing to.
ASKER
I gave 350 to Outin. I am using this one.
50 each to Dr*SQL and one solution from Catchmeifuwant,
25 each for geotiger and other solution from Catchmeifuwant.
Thanks everybody.
50 each to Dr*SQL and one solution from Catchmeifuwant,
25 each for geotiger and other solution from Catchmeifuwant.
Thanks everybody.
Thank You !
Glad I could help!
--
Outin
--
Outin