Mysql – Creating MySQL table with 100+ columns

MySQLmysql-8.0unpivot

I have a dataset to be loaded to MySQL that contains 100+ columns of assorted data items. I'd planned to pivot the dataset to be row-oriented in the vein of RDBMS best practice but absent a PIVOT construct as exists in SQL Server within MySQL, and also not being convinced of the benefit of doing so, I'm thinking my best bet will be to load the data as is.

With that, I need to create the corresponding table in MySQL but wonder if there's any way to avoid having to create 100+ column names/types manually as part of a CREATE TABLE statement. I've included a sample of the dataset (and a subset of columns) below:

ticker  dimension   calendardate    datekey reportperiod    lastupdated accoci  assets  assetsavg   assetsc assetsnc    assetturnover   bvps    capex   cashneq cashnequsd  cor consolinc   currentratio    de  debt    debtc   debtnc  debtusd deferredrev depamor
A   ARQ 1999-12-31  2000-03-15  2000-01-31  2019-12-20  53000000    7107000000      4982000000  2125000000      10.219  -30000000   1368000000  1368000000  1160000000  131000000   2.41    0.584   665000000   111000000   554000000   665000000   281000000   96000000
A   ARQ 2000-03-31  2000-06-12  2000-04-30  2019-12-20  -4000000    7321000000      5057000000  2264000000      10.27   -95000000   978000000   978000000   1261000000  166000000   2.313   0.577   98000000    98000000    0   98000000    329000000   103000000
A   ARQ 2000-06-30  2000-09-01  2000-07-31  2019-12-20  -6000000    7827000000      5344000000  2483000000      10.821  -222000000  703000000   703000000   1369000000  155000000   2.129   0.597   129000000   129000000   0   129000000   361000000   146000000
A   ARQ 2000-09-30  2001-01-17  2000-10-31  2019-12-20  -5000000    8425000000      5655000000  2770000000      11.648  -355000000  996000000   996000000   1732000000  305000000   2.05    0.6 110000000   110000000   0   110000000   372000000   150000000
A   ARQ 2000-12-31  2001-03-19  2001-01-31  2019-12-20  33000000    9208000000      5461000000  3747000000      12.178  -114000000  433000000   433000000   1449000000  154000000   1.779   0.662   556000000   556000000   0   556000000   405000000   139000000
A   ARQ 2001-03-31  2001-06-14  2001-04-30  2019-12-20  15000000    9080000000      4998000000  4082000000      12.316  5000000 809000000   809000000   1406000000  96000000    1.613   0.617   773000000   773000000   0   773000000   377000000   166000000
A   ARQ 2001-06-30  2001-09-14  2001-07-31  2019-12-20  -11000000   8622000000      4656000000  3966000000      11.954  -259000000  999000000   999000000   1146000000  -219000000  1.66    0.571   794000000   794000000   0   794000000   386000000   196000000
A   ARQ 2001-09-30  2002-01-22  2001-10-31  2019-12-20  0   7986000000      4799000000  3187000000      12.275  -226000000  1170000000  1170000000  1337000000  143000000   2.397   0.411   6000000 6000000 0   6000000 279000000   233000000
A   ARQ 2001-09-30  2002-02-01  2001-10-31  2019-12-20  0   7986000000      4799000000  3187000000      12.275  -226000000  1170000000  1170000000  1337000000  143000000   2.397   0.411   6000000 6000000 0   6000000 279000000   233000000
A   ARQ 2001-12-31  2002-03-06  2002-01-31  2019-12-20  -5000000    8558000000      5467000000  3091000000      11.652  -69000000   2188000000  2188000000  878000000   -315000000  3.293   0.586   1150000000  0   1150000000  1150000000  269000000   199000000
A   ARQ 2001-12-31  2002-05-24  2002-01-31  2019-12-20  -47000000   8493000000      5467000000  3026000000      11.562  -69000000   2188000000  2188000000  878000000   -315000000  3.293   0.587   1150000000  0   1150000000  1150000000  269000000   199000000
A   ARQ 2002-03-31  2002-06-05  2002-04-30  2019-12-20  -43000000   8238000000      5260000000  2978000000      11.052  -86000000   2235000000  2235000000  854000000   -253000000  3.269   0.606   1150000000  0   1150000000  1150000000  283000000   169000000
A   ARQ 2002-06-30  2002-09-13  2002-07-31  2019-12-20  -1000000    8437000000      4979000000  3458000000      10.732  -58000000   2105000000  2105000000  846000000   -228000000  2.543   0.687   1150000000  0   1150000000  1150000000  278000000   180000000
A   ARQ 2002-09-30  2002-12-20  2002-10-31  2019-12-20  -149000000  8203000000      4880000000  3323000000      9.951   -88000000   1844000000  1844000000  1116000000  -236000000  2.238   0.773   1150000000  0   1150000000  1150000000  244000000   187000000
A   ARQ 2002-12-31  2003-03-12  2003-01-31  2019-12-20  -108000000  7770000000      4559000000  3211000000      9.234   -45000000   1754000000  1754000000  883000000   -369000000  2.254   0.787   1150000000  0   1150000000  1150000000  256000000   82000000

enter image description here

Would welcome other thoughts or approaches.

Best Answer

The data sample you gave does not look like it was pivoted. It does not show what I would think of as an "array" of similar things -- such months of a year or years or userids.

As long as there are 100 distinct things related to whatever the PRIMARY KEY is, then 100 columns is OK.

You may be confusing "pivot" and "entity-attribute-value". The latter shows up in "shopping" schemas, where the "products" have random and dissimilar attributes.

Common examples of an array going across columns: several phone numbers; several locations (address, city, country, etc). Those should be unpivoted.

If you do end up needing to unpivot, I suspect there are decent examples of such if you follow the pair of tags "[mysql] [unpivot]" either on this site or, more likely, on stackoverflow.com .

For further discussion, please provide the 100-column CREATE TABLE and indicate which things you are thinking about unpivoting.

Related Question