UNPIVOT operator page 1
As its name suggests, UNPIVOT performs the opposite operation to PIVOT, presenting data stored in a table row as a single column. In our example from the previous section, we used the PIVOT operator to convert to a row the set of records retrieved by the following query:
The result looked as follows:
We can restore the initial form of the data by applying an unpivot transformation to the pivot query:
Note that the name avg_ can’t be used in the UNPIVOT operator, because it is already used in the PIVOT operator; thus, I used a new name avg__ and finally aliased it to fully restore the original data obtained by grouping.
Now, let’s go on with a more meaningful example. Suppose the information about flight 1100 has to be presented as follows:
Since here, the data stored in a record of the table is transformed into a column, using the UNPIVOT operator suggests itself. One thing is to be noted though. All values in this column must be of the same type. Since, in our example, values from different columns of the original table are put in this one column, they all have to be converted to a single type. Even more, not just their size (space allocated for storage of the value) should be the same, as well.
For our case, a string type is best suited. Since the columns town_from and town_to already are of type char(25), let’s convert all other values to it:
Here we are at the same time transformed the time of departure / arrival, removed the part of date:
The rest, I hope, clear from the code:
Column with the name spec is used to display the names of parameters, and column info contains parameters themselves. The result of the query already has been presented in the task statement.