How to Transpose Rows to Columns on Multiple Fields in Each Group

#sql #database #data #development

We have a database table FACTTABLE, which has data as follows:

We are trying to group the table by ID and transpose rows to columns on multiple fields. Below is the desired result:

The target is to enter VALUE and CURRENCY values under "TYPE" and "TYPE"1 columns correspondingly and respectively, converting them to row values.

SQL written in SQL Server:



WITH CTE AS

(

  SELECT UP.ID,

         UP.TYPE + '_' + LEFT(UP.VALUETYPE, 4) AS NEW_CODE,

         UP.VALUE1

  FROM FACTTABLE

  UNPIVOT (VALUE1 FOR VALUETYPE IN (VALUE, CURRENCY)) UP

)

SELECT P.ID,

       P.TOT_AMT_CURR                       AS TOT_AMT,

       P.TOT_AMT_VALU                        AS TOT_AMT1,

       P.AMT_AVAL_CURR                     AS AMT_AVAL,

       P.AMT_AVAL_VALU                      AS AMT_AVAL1,

       P.DRWN_AMT_CURR                   AS DRWN_AMT,

       P.DRWN_AMT_VALU                   AS DRWN_AMT1,

       P.MTD_DRWN_CURR                   AS MTD_DRWN,

       P.MTD_DRWN_VALU                   AS MTD_DRWN1

FROM CTE

PIVOT (MAX(VALUE1) FOR NEW_CODE IN ([TOT_AMT_CURR], [TOT_AMT_VALU],

                                   [AMT_AVAL_CURR], [AMT_AVAL_VALU],

                                   [DRWN_AMT_CURR], [DRWN_AMT_VALU],

                                   [MTD_DRWN_CURR], [MTD_DRWN_VALU])) P

It is rather easy to approach the task in our natural way of thinking. Just convert VALUE and CURRENCY columns to rows – correspond VALUE whose TYPE is TOT_AMT to TOT_AMT and CURRENCY whose type is TOT_AMT to TOT_AMT1, and so on – and then perform row-to-column transposition. SQL does not support dynamic transpositions. It can manage one over a few fixed columns. When the number of columns increases, coding becomes difficult and SQL gets bloated as all columns need to be enumerated during the transposition process. When columns are dynamic too, the solution becomes even more complex.

It is simple and easy to achieve the algorithm in the open-source esProc SPL:

As a professional data computing engine, SPL is based on ordered sets and provides all-around abilities for performing set-oriented operations. It combines merits of Java and SQL and is convenient for handling various transposition problems.

Python + AI + Spreadsheet

Chat with your data and get insights in seconds with the all-in-one spreadsheet that connects to your data, supports code natively, and has built-in AI.

Try Quadratic free