SQLite: How to create a new table from another table when the columns are disparate but linked by on-CodePudding

Say I have a new table like such where there is no values yet:

key	uuid	dog	cat	deer	etc

and i have a populated table like such where it has values that i want to correlate to the new empty table:

key	uuid	format	status
1	uuid1	dog	hairy
2	uuid1	cat	fluffy
3	uuid2	dog	shaved
4	uuid3	deer	smooth

what i want to do is take each "format" from table 2 and create a new column in table 1 where "status" from table 2 is the value of the new "format" column in table one. Here is what i want the table to look like assuming the above tables are what im working with:

key	uuid	dog	cat	deer	etc
1	uuid1	hairy	fluffy	null	other value
2	uuid2	shaved	null	null	other value
3	uuid3	null	null	smooth	other value

The extra tricky part is in table 2, uuid1 can have more or less "format" values than say uuid2 and visa versa continuing on to like 50k uuids so i need to fill the other columns with a null or falsey value

Is this possible or am I working with too ridiculous of data to make it happen?

CodePudding user response：

Since you have created the new table this means that you already know the possible values of the column format.
In this case you can use conditional aggregation to populate the table:

INSERT INTO table2 (uuid, dog, cat, deer)
SELECT uuid,
       MAX(CASE WHEN format = 'dog' THEN status END),
       MAX(CASE WHEN format = 'cat' THEN status END),
       MAX(CASE WHEN format = 'deer' THEN status END)
FROM table1
GROUP BY uuid;

See a simplified demo.