Preserving the order of columns when getting a list of column names

[meredydd]

Hi @vquach.

@vquach

I have a data table with columns where every time the user adds a new product, it auto creates a column. This is intended to allow the user to be able to track usage by a specific date, or date range.

Generally, this is an awkward way to do things that is going to cause some pain! In a database, the set of columns is supposed to stay fixed, and you represent data with additional rows. That is to say, it’s “long” data, rather than “wide” data.

So, instead of:

date	qty_product_A	qty_product_B	qty_product_C
2021-01-01	1	2	3

you should make a table like this, which represents the change for each product individually:

date	product	qty
2021-01-01	A	1
2021-02-15	B	2
2021-03-01	C	3

That way, you don’t have to rely on column ordering (which, as you have discovered, isn’t predictable!)

Also, as a general performance tip, and making a guess about your application, I would recommend creating two tables - one with the current quantity of each product, and one for the historical data. That way, it’s always easy to check current stock levels (a common operation), but you can still query historical data.