Home > Enterprise >  Is it better to pivot data in SQL or in my application code?
Is it better to pivot data in SQL or in my application code?

Time:12-02

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, ARP is just less data than three rows with date, 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.

  • Related