* How to Transpose Rows to Columns on Multiple Fields in Each Group in SQL?

 

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

ID

TYPE

VALUE

CURRENCY

ABC

TOT_AMT

3

5.7702

ABC

AMT_AVAL

1

1.9234

ABC

DRWN_AMT

2

3.8468

ABC

MTD_DRWN

2

3.8468

DEF

TOT_AMT

3

5.7702

DEF

AMT_AVAL

1

1.9234

DEF

DRWN_AMT

2

3.8468

DEF

MTD_DRWN

2

3.8468

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

ID

AMT_AVAL

AMT_AVAL1

DRWN_AMT

DRWN_AMT1

MTD_DRWN

MTD_DRWN1

TOT_AMT

TOT_AMT1

ABC

1

1.9234

2

3.8468

2

3.8468

3

5.7702

DEF

1

1.9234

2

3.8468

2

3.8468

3

5.7702

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:

A

1

=connect("mssql")

2

=A1.query@x("SELECT * FROM FACTTABLE")

3

=A2.news([VALUE,CURRENCY];ID,TYPE,~:VALUE).run(if(#%2==0,TYPE=TYPE/"1"))

4

=A3.pivot(ID;TYPE,VALUE)

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.

Q & A Collection

https://stackoverflow.com/questions/64132722/pivot-operator-on-two-columns