As the name of the operator, UNPIVOT performs the reverse of the PIVOT operator, i.e. represents the data stored in the table row in one column. In example, considered in previous section, we converted table into string using PIVOT operator, the table was obtained using following query:
As result, has received the following representation:
Initial results we can get, if applied to the pivot-query unpivot-transformation:
Note that the name avg_ can not be used in the UNPIVOT operator, because it is already used in PIVOT operator, so I used new name avg__, which would then assign an alias to fully reconstruct the result obtained with grouping.
Let us now consider a more substantial example. Suppose you want to display information on 1100 trip in the following form:
Since the information from the row of the table is transformed into a column, it begs the use of the operator UNPIVOT. It should make one remark. The values in this column should be the same type. As in this column in our example, the values are collected from different columns of the source table; you need to convert them to a single type. Moreover, not only the types must match but and size.
Common type in our case is a string type. Since the columns town_from and town_to already have type char (25), then convert all to this type:
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.