Updating 2d table with 1d table data

aloysius koh

Example of what table 1 will look like:

I want to pull data from Table 1 and regroup them accordingly to Table 2 so i was working my way through. Firstly, I want to pull all data from Table 1 into one element of Table 2 ("FxMotion/example_1") but it doesnt seem to be working:

UPDATE T2
SET T2.'FxMotion' = (
SELECT 'Title'
FROM T1
) WHERE 'Blocks' = "example_1";
SELECT * FROM T1

Stiltsoft support

Hi @aloysius koh ,

If I understand correctly, the 'Title' column consists of several parts (something like date, description, typy) that are divided by " - ". So, we can split these parts into separate columns and then aggregate them as you need.

The page structure will look as following:

With the help of the Table Transformer macro we split the 'Title' column:

SELECT *,
T1.'Title'->split(" - ")->0 AS 'Date',
T1.'Title'->split(" - ")->1 AS 'Description',
T1.'Title'->split(" - ")->2 AS 'Type'
FROM T*

Then we can aggregate this table with the help of the Pivot Table macro (I've chosen these particular settings but you can adjust them as you need):

Hope it helps your case.