*How to Transpose Columns in Each Group to a Single Row in SQL

 

We have a database table STAKEHOLDER as follows:

CLASS

NAME

SID

1

name1

001

1

name2

002

2

name3

003

2

name4

004

2

name5

005

We are trying to group the table by CLASS and convert all columns to a same row. Below is the desired result set:

CLASS

NAME1

SID1

NAME2

SID2

NAME3

SID3

1

name1

001

name2

002

2

name3

003

name4

004

name5

005

SQL code written in Oracle:

WITH CTE AS(

SELECT

            UP.CLASS,

            UP.NS || UP.RN AS NSR,

            UP.VAL

FROM

            (

            SELECT

                        ROW_NUMBER ()

         OVER (

           PARTITION BY S.CLASS

            ORDER BY

                        S.CLASS) RN,

                        S.*

            FROM

                        STAKEHOLDER S

            ORDER BY

                        CLASS,

                        SID) SS

UNPIVOT (VAL FOR NS IN (NAME, SID)) UP

)

SELECT

            *

FROM

            CTE

PIVOT(MAX(VAL) FOR NSR IN ('NAME1' AS NAME1,

            'SID1' AS SID1,

            'NAME2' AS NAME2,

            'SID2' AS SID2,

            'NAME3' AS NAME3,

            'SID3' AS SID3))

This is not difficult if we handle it with our natural way of thinking. After grouping the table by CLASS, we convert NAME and SID columns into rows and create names commanding values to be converted to columns. Format of names is the original column name + number of subgroups, like NAME1, SID1, NAME2, SID2… for group 1 and NAME1, SID1, … for group2. Then we concatenate groups and transpose row to columns. The problem is SQL does not support dynamic row-to-column/column-to-row transposition. When the number of columns is small and columns are fixed, the language can mange to do the transpositions. As the number of columns increases, the scenario becomes more and more awkward. Enumerating all columns to be converted is complicated and SQL code becomes bloated. If columns are dynamic, SQL needs to turn to complex and roundabout ways to handle them.

Yet, it is really easy to code the transposition task with the open-source esProc SPL:

A

1

=connect("ORACLE")

2

=A1.query@x("SELECT * FROM STAKEHOLDER ORDER BY CLASS,SID")

3

=A2.fname().m(2:)

4

=A2.group@o(CLASS)

5

=A4.conj(~.news(A3;CLASS,A3(#)/A4.~.#:COL,eval(~):VAL))

6

=A5.pivot(CLASS;COL,VAL)

SPL is the specialized data computing engine that is based on ordered-sets. It offers the all-round abilities for performing set-oriented operations, supports stepwise coding, and provides intuitive solutions. Instead of enumerating columns, SPL can automatically scale up, making it convenient to deal with various transposition tasks.

Q & A Collection

https://stackoverflow.com/questions/64138971/how-to-merge-2-tables-into-1-row-with-multiple-entries-from-second-table-in-ora