I have a column of data (RoomNum) that is had multiple entries listing rooms seperated by commas. So it would look like "2.201, 2.203, 2.506"
I need to join by the room number to another data set so I am trying to do a custom SQL query to split the data so every row is has only one room number in it.
Does anyone have any guidance? I've tried using STRING_SPLIT() but Tableau keeps putting up an error saying it's not recognized
Hi Emily,
This is a more existential data engineering question. If you are to shear each row down to 1 room number, then your data should grow by the sum of the split.
If you're looking to join a mapping table to room_num, the easiest way to do this in SQL would be to say that:
SELECT
...
FROM room_num rn
OUTER JOIN mapping_table mt on mt.room_number like CONCAT('%'+rn.room_num+'%')
The algorithmic complexity of this approach could make this an ineffective solution.
