sqlsql-serversqlperformance

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


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?


Solution

  • 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

    The pros of pivoting in your app

    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.