How to Transpose Columns in Each Group to a Single Row

#sql #development #spl

We have a database table STAKEHOLDER as follows:

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

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:

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.