# 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. 