|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
July 27th, 2004, 04:04 PM
|
|||
|
|||
|
Concatenating multiple rows of a column into one
Hi,
I have a table which looks as follows ID Name ----------- 1 ABC 1 DEF 1 GHI 2 JKL 2 MNO 2 PQR 2 STU 3 VW 3 XYZ I want to write a select statement which gives me the following ouput ID Names ----------------- 1 ABC-DEF-GHI 2 JKL-MNO-PQR-STU 3 VW-XYZ Is there a way this can be achieved by writing a SELECT statement and not PL/SQL code? If so, can some one please let me know. I appreciate your help Thanks, OracleGreenHorn
|
|
#2
July 28th, 2004, 03:15 PM
|
||||
|
||||
|
The only way I could think of doing that is if you were able to loop through the contents of the table and check while the id's were equal, concatenate, otherwise go to the next one.
|
|
#3
July 28th, 2004, 03:44 PM
|
|||
|
|||
|
Not really
Unfortunately, there is no way to do this for the general case, without resort to a procedural function.
If you desperately need a non-procedural solution, there is an "ugly" fix. The only criteria is that you need to know the MAXIMUM number of rows for the second column for each value in the first column. In your sample data, you can see that the value "2" in the first column has the largest number of second column entries (a total of 4). If 4 is always the largest number of column2 entries, the following query will work: Code:
SELECT T1.C1, REPLACE(T1.C2 || '-' || NVL(T2.C2,' ') || '-' || NVL(T3.C2,' ') || '-' || NVL(T4.C2,' ') ,'- ',NULL) str FROM (SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn FROM tab) T1, (SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn FROM tab) T2, (SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn FROM tab) T3, (SELECT c1, c2, row_number() over (PARTITION BY c1 ORDER BY c2) rn FROM tab) T4 WHERE T1.RN = 1 AND T2.RN(+) = 2 AND T3.RN(+) = 3 AND T4.RN(+) = 4 AND T1.C1 = T2.C1(+) AND T1.C1 = T3.C1(+) AND T1.C1 = T4.C1(+) (where tab is your sample table, and c1 and c2 are the two columns of tab). This can be expanded by simply adding and joining T5, T6, ... , TN, where "N" = "the largest number of values for c2 for any given c1".
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Concatenating multiple rows of a column into one |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
