T-SQL: PIVOT Ordered pair Columns
This article discusses how we can pivot ordered pair columns.
Vocabulary
Ordered pair
In mathematics, an ordered pair (a, b) is a pair of mathematical objects. The order in which the objects appear in the pair is significant: the ordered pair (a, b) is different from the ordered pair (b, a) unless a = b. Ordered pairs are also called 2-tuples. (More info)
Ordered pair columns
We can also use this mathematical concept in the design of our databases. We can use two columns, for example, in accounting transaction tables to know what Entity (s) called what accounting transaction.
Problem definition
Assume that we have a transaction table with this DDL script:
IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL
DROP TABLE #Table ;
CREATE TABLE #Table
(
Id INT ,
RowNumber INT ,
Sequence INT ,
EntityTypeId INT ,
EntityId INT
);
We populate this table with the following code:
INSERT #Table
( Id ,
RowNumber,
Sequence ,
EntityTypeId ,
EntityId
)
VALUES ( 1, 1, 1, 101, 1500),
( 1, 1, 2, 102, 2500),
( 1, 1, 3, 103, 3500),
( 1, 1, 4, 104, 4500),
( 1, 2, 2, 120, 1200) ;
Our problem is creating a view to pivot ordered pair columns as shown in next figure:
Solution
This solution has two phases. The first phase involves the UNPIVOT operator. Then in a second phase, we use the produced column in the former phase and PIVOT operator.
Now we can see two phases separately:
Initial phase
In this phase, we combine Sequence column data with each of the pair columns and get rid of Sequence column. Next code shows this phase:
;WITH
cte1 AS
(
SELECT Id ,
RowNumber,
Sequence ,
EntityTypeId ,
EntityId
FROM #Table
)
SELECT Id,
RowNumber,
ColumnTitle + LTRIM(RTRIM(STR(Sequence))) AS ColumnTitle,
ColumnData
FROM cte1
UNPIVOT (ColumnData FOR ColumnTitle IN ([EntityTypeId], [EntityId])) AS UP
We can see this combination in the next figure.
Final phase
Now, we can use these two new columns and pivot operator to achieve our desired result. This is really the simple phase. Next code shows this phase:
SELECT Id ,
RowNumber ,
[EntityTypeID1] FirstEntityTypeID ,
[EntityID1] FirstEntityID ,
[EntityTypeID2] SecondEntityTypeID ,
[EntityID2] SecondEntityID ,
[EntityTypeID3] ThirdEntityTypeID ,
[EntityID3] ThirdEntityID ,
[EntityTypeID4] FourthEntityTypeID ,
[EntityID4] FourthEntityID
FROM cte2 PIVOT ( MAX(ColumnData) FOR ColumnTitle IN ( [EntityTypeID1],
[EntityID1],
[EntityTypeID2],
[EntityID2],
[EntityTypeID3],
[EntityID3],
[EntityTypeID4],
[EntityID4] ) ) PV
Whole solution
Now we combine these two phases to see the whole solution in the next code:
;
WITH cte1
AS ( SELECT Id ,
RowNumber ,
Sequence ,
EntityTypeId ,
EntityId
FROM #Table
),
cte2
AS ( SELECT Id ,
RowNumber ,
ColumnTitle + LTRIM(RTRIM(STR(Sequence))) AS ColumnTitle ,
ColumnData
FROM cte1 UNPIVOT ( ColumnData FOR ColumnTitle IN ( [EntityTypeId], [EntityId] ) ) AS UP
)
SELECT Id ,
RowNumber ,
[EntityTypeID1] FirstEntityTypeID ,
[EntityID1] FirstEntityID ,
[EntityTypeID2] SecondEntityTypeID ,
[EntityID2] SecondEntityID ,
[EntityTypeID3] ThirdEntityTypeID ,
[EntityID3] ThirdEntityID ,
[EntityTypeID4] FourthEntityTypeID ,
[EntityID4] FourthEntityID
FROM cte2 PIVOT ( MAX(ColumnData) FOR ColumnTitle IN ( [EntityTypeID1],
[EntityID1],
[EntityTypeID2],
[EntityID2],
[EntityTypeID3],
[EntityID3],
[EntityTypeID4],
[EntityID4] ) ) PV
As illustrated in the next figure, we achieve our desired output result.
