We have a database table FIRSTTBL. Below is part of its data:
And we are trying to combine every six rows into one record. Below is part of the desired result:
It is a row-to-column transposition – converting PROPERTY field values of every six rows into the new column attributes, and get non-null values under the original STRING, INTEGER, DATETIME, BOLLEAN and XML columns to assign them to the corresponding new columns (while maintaining the original data types).
SQL：
ID,
FIRSTNAME,
…,
FLAG = CAST (FLAG AS INT),
…
FROM
(
SELECT
*
FROM
(
SELECT
f.ID,
f.PROPERTY,
f.STRING + f.”INTEGER” + f.DATETIME + f.BOLLEAN + f.XML AS COLS
FROM
FIRSTTBL f)
PIVOT(
min(COLS) FOR PROPERTY IN
(
‘firstname’ AS firstname,
‘lastname’ AS lastname,
‘birthdate’ AS birthdate,
‘address’ AS address,
‘flag’ AS flag,
‘number’ AS “NUMBER”
)
)
)
According to the original table, there is one and only one non-null value among STRING, INTEGER, DATETIME, BOLLEAN and XML columns for any row, so we just need to get the first non-null value and assign it to the corresponding new column. It is not difficult to perform the transposition using PIVOT function, except that we need to handle different data types according to the SQL rule, which requires that each column have a consistent type. For this task, first we need to convert the combined column values into a string, perform row-to-column transposition, and then convert string back to the proper types. When there are a lot of columns, the SQL statement can be tricky, and dynamic requirements are even hard to achieve.
Yet it is easy to write the code using the open-source esProc SPL:
SPL does not require that data in the same column have consistent type. It is easy for it to maintain the original data types while performing the transposition.
