I have the following sample data:
key | source_string
---------------------
1355 | hb;am;dr;cc;
3245 | am;xa;sp;cc;
9831 | dr;hb;am;ei;
What I need to do:
Find strings from a fixed list ('hb','am','dr','ac') in the source_string
Create 3 new attributes and assign the found strings to them randomly but fixed (no difference after query re-execution)
If possible no subqueries and all in one SQL SELECT statement
The solution should look like this:
key | source_string | t_1 | t_2 | t_3
---------------------------------------
1355 | hb;am;dr;cc; | hb | dr | cc
3245 | am;xa;sp;cc; | cc | am |
9831 | dr;hb;am;ei; | hb | dr | am
My thought process:
I wanted to return the strings that occurred per row -> 1355: HB, am,dr, cc, (no idea how)
Rank them based on the key to having it randomly (maybe with rank() and mod())
Assign the strings based on their rank to the new attributes. At key 1355 4 attributes match, but only 3 need to be assigned, so the one left has to be ignored. (Everything in Postgres)
In my current solution, I created a rule for every case, which results in a huge query that is not desirable.