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?
format
values ahead of time to hard-code into your pivot clause or case expressions. That being said, it's a terrible usecase for an RDBMS, and double so for sqlite where there is nopivot
clause, and triple-so as the values you are pivoting are non-numeric. Personally I would do this transformation elsewhere, like in Python or what-have-you.