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?
CodePudding user response：
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.
The pros of pivoting in SQL
- Less data to transmit. A row with
date, Units Sold, # Customers, ARPis just less data than three rows withdate, dataItemName, dataItemValue. - If you create a view, you make it look like you are dealing with a normal table. Your queries get more readable and are less prone to errors.
The pros of pivoting in your app
- If you add a key, say dataItemName = 'highest price' all your queries (and hopefully your app, too) would stay the same, which is what the key/value design is all about after all.
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.