Hi All,
So I have a table where I want to conatinate the column with comma seprated but the only challenge is the number of columns could increase or decrease everytime.
So for example below.
ID | name | source id 1 | source id 2 | source id 3 | final id 1 | final id 2 |
123 | ABC | 1 | 3 | 1 | 2 | 1 |
234 | GHD | 2 | 3 | |||
12 | KLF | 3 | 4 | 4 | 3 | |
1 | KNI | 4 | 6 | 4 | 5 | 4 |
234 | KNR | 5 | 7 | 1 | 6 | 5 |
1. I want to concatenate all the source columns in to one and same for final id column
2. The number in front of Source id and final id will always change
3. Starting two fields should not change.
Output example:
ID | name | Source ID | Final ID |
123 | ABC | 1,3,1 | 2,1 |
234 | GHD | 2 | 3 |
12 | KLF | 3,4 | 4,3 |
1 | KNI | 4,6,4 | 5,4 |
234 | KNR | 5,7,1 | 6,5 |
@Sshasnk
We can first remove the postfix of digits with RegEx_Replace function, the do a CrossTab.
@Sshasnk
one way of doing this