SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Collapse multiple column values from more than 1 row to single row

J1604Jul 15 2020 — edited Jul 16 2020

Good Afternoon Gurus;

I am working on a situation where I need to collapse distinct values in different fields in subsequent rows into one row based on unique combinations

Here it is:

with t as

(

select '18' sp_pl, '217661211' ti, '204D00000X' tx_1, '' tx_2, '' tx_3 from dual

union all

select '18' sp_pl, '217661211' ti, '' tx_1, '20988823Y' tx_2, '' tx_3 from dual

union all

select '18' sp_pl, '217661211' ti, '' tx_1, '' tx_2, '45533823Q' tx_3 from dual

)

select * from t;

| SP_PL | TI | TX_1 | TX_2 | TX_3 |
| 18 | 217661211 | 204D00000X | | |
| 18 | 217661211 | | 20988823Y | |
| 18 | 217661211 | | | 45533823Q |

My output would have to collapse tx_1, tx_2, and tx_3 values into one row based on unique SP_PL and TI combination

SP_PL TI TX_1 TX_2 TX_3

18 217661211 204D00000X 209888823Y 45533823Q

Please guide me

J1604

This post has been answered by Solomon Yakobson on Jul 15 2020

Jump to Answer

Added on Jul 15 2020

5 comments

1,240 views