I have a very long & narrow table in a MSSQL Database, one that looks a bit like:
date | dataItemName | dataItemValue |
---|---|---|
2021-01-01 | Units Sold | 20 |
2021-01-01 | # Customers | 2948 |
2021-01-01 | ARP | 19 |
2021-01-02 | Units Sold | 146 |
2021-01-02 | # Customers | 157 |
2021-01-02 | ARP | 32 |
And I'm trying to get a table of the form:
date | Units Sold | # Customers | ARP |
---|---|---|---|
2021-01-01 | 20 | 2948 | 19 |
2021-01-02 | 146 | 157 | 32 |
My question is: are there good reasons to pivot the table in SQL (creating a view or materialized table) vs. pulling the raw data and doing the pivot in my application?
You are dealing with a key/value table. Fortunately it seems that at least the value is always numeric, so the dataItemValue
column can be numeric and values like Units Sold = 'many'
or # Customers = 'I don''t know'
are thus not possible. But key/value tables are always a nuisance to work with.
date, Units Sold, # Customers, ARP
is just less data than three rows with date, dataItemName, dataItemValue
.Ideally you use a key/value table, because the keys are irrelevant to your app. Say, you have products, and some have a collar type, some a maximum temparature, some a maximum speed. Your product establishing app would allow your employees to enter the data and your selling app or Webste would display the data. Neither app needs to know what a collar type or a maximum temparature means. In that case you would select the (unknown) raw data and your app would do the pivoting (if needed at all).
In your case, however, the key/value table doesn't seem really appropriate. You want to deal with certain attributes as if they were real columns in a table and your app shall know what Units Sold
and # Customers
means. In that case you would ideally use a normal table with these columns. If you are forced to work with a key/value table instead, make the best of it. Pivot in SQL, ideally in a view, so you would be oblivious of the inapproriate table design decision.