Array to columns ( or rows )

RADSr — Mon, 26 Feb 2024 23:07:22 GMT

I have a DB table which stores ( as a string ) values in an array. I don't like it, but I don't have any say in the matter nor do I have access to the DB to rework it there.

What I'd like to do is query the table ( e.g. select ID, Name, ArrayField from TABLE ) but pull "ArrayField" into either multiple columns ( challenging because the max number of possible values is not static ) or rows ( select ID, ArrayFieldValue from TABLE ) such that the key would be the ID/ArrayFieldValue combination.

I can get there, but I'm not at all certain that my path isn't the Rte 66 to someone else's I-55

If you were faced w/ this challenge what approach would you take?

Re: Array to columns ( or rows )

JoeM — Tue, 27 Feb 2024 14:27:45 GMT

Randy - I've run into a variation of this conundrum myself (that may or may not help you here)in a Bulk API. I'm not sure if this is something you've been digging into, but I found the column variability to categorically depend on the object tracked by the API. After some investigation, I found the variability to reflect 10 different conditions, but the conditions themselves were not variable amongst themselves. Therefore, I wrote filtered extracts for those 10 conditions so each table kept a fixed schema.

topic Re: Array to columns ( or rows ) in Data & Schema Discussions

Array to columns ( or rows )

Re: Array to columns ( or rows )