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