Pivot data using SQL, ES & TypeScript & Generics (ES2018)

Many times we need to convert our raw data into Pivot, transposing rows values as attribute/column and mapping another property/column data corresponding “value”. To understand better with database terminology — Transposing a matrix means reversing rows and columns. A row represents an entity, and a column an attribute of an entity and we need swap it around.

Consider below example (Image 1.1)

• Product list with specific category price — where data is duplicate for product ID due to different category prices for the same product.
• Purpose is to yield unique Products (unique) with respective category prices, as shown below pic.

Image 1.1

Within in 3 tier application we can resolve or handle this

• Within backend layer (Database — SQL/Query/Cursor)
• Within Presentation or Application layer (code/programming)

Approach I — with SQL query

select pd.productId,
  (case when pd.category = 'CAT1' THEN pd.price END) as CAT1,
  (case when pd.category = 'CAT2' THEN pd.price END) as CAT2,
  (case when pd.category = 'CAT3' THEN pd.price END) as CAT3,
  (case when pd.category = 'xxx' THEN pd.price END) as XXX,
from product_category_price as pd  

In this SQL approach, we could nearly solves our purpose, however the only one downside that all the possible data values (i.e. categories) must be known in prior, to build our ‘case when’ statement and if added new categories (i.e. CAT4, CAT5) require change in our SQL query.

Get Ranga’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Although within data base there are many ways to solve this pivoting using CURSOR, however common opinion in database community is to stay away from SQL Server “Cursors”, however cursors are still useful in many ways. Another solution of transposing rows into columns is by using XML. The XML solution to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation.

In general, the presentation of data is better handled by the application layer. So one of the idea is that fetch/read table is (raw) and let the application code handle this pivoting.

Approach II — using code (ES2018 — JavaScript & TypeScript)

[A] Let’s tackle this using plain vanilla ES2018

[B] Let’s improve same using powerful TypeScript & Generics

Typescript provides type safety and better control & with the generics it offers a great deal of reusability — one able to write code that can work over a variety of types rather than a specific also safe guards of type and object key/properties.

Hope this helps in some ways.

Kishor!