* How to Handle Different Data Types during Row-to-Column Transposition in SQL?

 

We have a database table FIRSTTBL. Below is part of its data:

ID

PROPERTY

TYPE

STRING

INTEGER

DATETIME

BOLLEAN

XML

1

firstname

NVARCHAR

John

null

null

null

null

1

lastname

NVARCHAR

Doe

null

null

null

null

1

birthdate

DATETIME

null

null

1986-09-16

null

null

1

address

XML

null

null

null

null

eyJh…

1

flag

BIT

null

null

null

1

null

1

number

INT

null

20

null

null

null

2

And we are trying to combine every six rows into one record. Below is part of the desired result:

ID

FIRSTNAME

LASTANME

BIRTHDAY

ADDRESS

FLAG

NUMBER

1

John

Doe

1986-09-16

eyJh…

1

20

2

 

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

SELECT

            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:

A

1

=connect("MSSQL")

2

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

3

=A2.pivot(ID;PROPERTY,~.array().m(4:).ifn();"firstname":"FIRSTNAME","lastname":"LASTANME","birthdate":"BIRTHDAY",

"address":"ADDRESS","flag":"FLAG","number":"NUMBER")

 

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.

 

Q & A Collection

https://stackoverflow.com/questions/64101227/sql-server-pivot-values-with-different-data-types