Currently i’m using Postgres and below is my table:
| ordrnbr | vehiclename | description | id | totprice | ind_price |
|---|---|---|---|---|---|
| 132 | tesla | 123@21000@bsd@23000@wer@41000 | 1saR | 84000 | |
| 135 | toyota | abc@21000@bsd@2300 | 2aqE | 23300 | |
| 138 | honda | abc@2100 | 3xDY | 2100 | |
| 141 | kia | text | 6iPQ | 10000 |
Here in the description column, it is represented as key@value (when delimiter ‘@’ is present) i.e., k1@v1@k2@v2@k3@v3
I need to split the description column based on the delimiter @ and retrieve the values and determine if each value is >=10000. If the value is >=10000, then i need to duplicate that row and suffix ordrnbr by -1,-2 and -3 respectively depending upon the number of txn’s which are more than 10000 for that ordrnbr and the ind_price column should have this value captured i.e.,
| ordrnbr | vehiclename | description | id | totprice | ind_price |
|---|---|---|---|---|---|
| 132-1 | tesla | 123@21000@bsd@23000@wer@41000 | 1saR | 84000 | 21000 |
| 132-2 | tesla | 123@21000@bsd@23000@wer@41000 | 1saR | 84000 | 23000 |
| 132-3 | tesla | 123@21000@bsd@23000@wer@41000 | 1saR | 84000 | 41000 |
| 135-1 | toyota | abc@21000@bsd@2300 | 2aqE | 23300 | 21000 |
| 141-1 | kia | text | 6iPQ | 10000 | 10000 |
If the @value is less than 10000 then it needs to be filtered out.
I tried using STRING_TO_ARRAY for splitting on description column based on delimiter @ and using unnest to duplicate records.
Can someone please help me in finding a solution as i’m really struggling to find one. Appreciated your help on this please.