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.
Top comments (1)
Learn more about open source SPL:github.com/SPLWare/esProc