ewang1205
asked on
Concatenate rows into one row - Oracle 8i
I have the data like:
Col1 Col2 Col3 Col4 Col5
111 a b 1 YYY
111 a b 2 XXX
111 a b 3 ZZZ
111 c d 1 YY
111 c d 2 XX
222 e f 1 ZZ
222 e g 1 YZ
333 h I 1 XY
I want to concatenate multiple rows into one row. The result should like:
Col1 Col2 Col3 Col5
111 a b YYYXXXZZZ
111 a b YYXX
222 e f ZZ
222 e g YZ
333 h I XY
Please provide a SQL solution. Thank you!
Col1 Col2 Col3 Col4 Col5
111 a b 1 YYY
111 a b 2 XXX
111 a b 3 ZZZ
111 c d 1 YY
111 c d 2 XX
222 e f 1 ZZ
222 e g 1 YZ
333 h I 1 XY
I want to concatenate multiple rows into one row. The result should like:
Col1 Col2 Col3 Col5
111 a b YYYXXXZZZ
111 a b YYXX
222 e f ZZ
222 e g YZ
333 h I XY
Please provide a SQL solution. Thank you!
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.
yes,
in any case you can use the above suggestions.
in any case you can use the above suggestions.
to perform something complex or aggregation , you better use pl/sql because if there is some modification which is needed at a later time then it will be easy to do it otherwise we might get stuck with the sql which might work correctly for the current case but does not allow modifying it to work for other cases.
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
This url is good with sample code examples for your understanding.
Thanks
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
This url is good with sample code examples for your understanding.
Thanks
Try this one:
SELECT COL1,COL2,
COL3,
MAX(REPLACE(SYS_CONNECT_BY _PATH(COL5 ,','),',') ) COL5
FROM TAB2
START WITH COL4 = 1
CONNECT BY PRIOR COL4+1 = COL4 AND PRIOR COL2 <> COL2 AND COL1 = PRIOR COL1
GROUP BY COL1,COL2,COL3
ORDER BY 1,2
SELECT COL1,COL2,
COL3,
MAX(REPLACE(SYS_CONNECT_BY
FROM TAB2
START WITH COL4 = 1
CONNECT BY PRIOR COL4+1 = COL4 AND PRIOR COL2 <> COL2 AND COL1 = PRIOR COL1
GROUP BY COL1,COL2,COL3
ORDER BY 1,2
I posted the wrong version above, use this one:
SELECT COL1,COL2, COL3,
MAX(REPLACE(SYS_CONNECT_BY _PATH(COL5 ,','),',') ) COL5
FROM TAB2
START WITH COL4 = 1
CONNECT BY PRIOR COL4+1 = COL4 AND PRIOR COL2 = COL2 AND COL1 = PRIOR COL1
GROUP BY COL1,COL2,COL3
ORDER BY 1,2
/
COL1 C C COL5
---------- - - ---------------
111 a b YYYXXXZZZ
111 c d YYXX
222 e f ZZ
222 e g YZ
333 h I XY
SELECT COL1,COL2, COL3,
MAX(REPLACE(SYS_CONNECT_BY
FROM TAB2
START WITH COL4 = 1
CONNECT BY PRIOR COL4+1 = COL4 AND PRIOR COL2 = COL2 AND COL1 = PRIOR COL1
GROUP BY COL1,COL2,COL3
ORDER BY 1,2
/
COL1 C C COL5
---------- - - ---------------
111 a b YYYXXXZZZ
111 c d YYXX
222 e f ZZ
222 e g YZ
333 h I XY
I'd bet there's no SYS_CONNECT_BY_PATH in 8i...
@GGuzdziol,
You're absolutely right. My bad not reading the whole post...
Cheers!
You're absolutely right. My bad not reading the whole post...
Cheers!
ASKER
sorry, I put the wrong result. The correct result should be:
Col1 Col2 Col3 Col5
111 a b YYYXXXZZZ
111 c d YYXX
222 e f ZZ
222 e g YZ
333 h I XY
Thanks.