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