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.

 


See Also