Differences

This shows you the differences between two versions of the page.

--- transformations:unpivot [2018/10/03 15:18] – dmitry
+++ transformations:unpivot [2021/07/19 02:16] (current) – [Examples] craigt
@@ Line 1: / Line 1: @@
-===== Unpivot =====
+{{ transformations:UnpivotAction.png}}
+====== UNPIVOT ======
+Category: Transform / Advanced\\
-Converts a matrix table into a straight table by rotating columns into [Labels] and [Data] columns. This transformation is opposite to the [[transformations:pivot|Pivot]] action.
+\\
+=====Description=====
+Converts a matrix table into a straight table by rotating columns into "Labels" and "Data" columns. This action is opposite to the [[transformations:pivot|Pivot]] action.\\
-EXAMPLE
+\\
+=====Use cases=====
+This action is used to convert a cross-table (or matrix) into a straight table. It can be used, for instance, to convert a cross-table from an Excel spreadsheet into a table suitable for exporting into a database.
-**Table:** Winter Olympics medals
+\\
+=====Action settings=====
+^Setting^Description^
+|Column name for labels|Enter a name for the column that will contain the column header labels from the matrix table.  |
+|Column name for data|Enter a name for the column that will contain the data values from the matrix table.  |
+|Unpivot all columns except these|Select fields that will not be unpivoted, but instead will be repeated down rows, as is, once for each column heading in the matrix table.  |
-^  Country  ^  winter 2010  ^  winter 2014  ^
+\\
-| Canada  |  26  |  25  |
+=====Remarks=====
-| United States  |  37  |  28  |
+Typically, any columns used for row labels in the source matrix table will be selected under "Unpivot all columns except these" to retain those values as row values in the unpivoted table.  Otherwise, those values will be listed in the result table with a "label" equal to the matrix table's column heading for the row labels, and data values equal to the row labels.
-| Norway  |  23  |  26  |
-| Germany  |  30  |  19  |
-**Transformation:** Unpivot
+\\
+=====Examples=====
+**Example:** Convert the matrix table, below, into a standard data table.\\
+\\ **Source table:** Winter Olympics medals
+^Country  ^winter 2010  ^winter 2014  ^
+| Canada  |  26|  25|
+| United States  |  37|  28|
+| Norway  |  23|  26|
+| Germany  |  30|  19|
+\\
+**Action parameters:**
+> Column name for labels is "Labels"
+> Column name for data is "Data"
+> Unpivot all columns except these = "Country" selected
+\\
+**Result table:**
+^Country  ^Labels  ^Data  ^
+| Canada  |winter 2010  |  26|
+| United States  |winter 2010  |  37|
+| Norway  |winter 2010  |  23|
+| Germany  |winter 2010  |  30|
+| Canada  |winter 2014  |  25|
+| United States  |winter 2014  |  28|
+| Norway  |winter 2014  |  26|
+| Germany  |winter 2014  |  19|
+Notice how the original set of Country values repeat for each value in Labels.\\
-**Result:**
+\\
+If no columns were selected under "Unpivot all columns except these", the resultant table would appear as below:\\
-^  Country  ^  Labels  ^  Data  ^
+**Result table:**
-| Canada  |  winter 2010  |  26  |
+^Labels  ^Data  ^
-| United States  |  winter 2010  |  37  |
+|Country  | Canada  |
-| Norway  |  winter 2010  |  23  |
+|Country  | United States  |
-| Germany  |  winter 2010  |  30  |
+|Country  | Norway  |
-| Canada  |  winter 2014  |  25  |
+|Country  | Germany  |
-| United States  |  winter 2014  |  28  |
+|winter 2010  |  26|
-| Norway  |  winter 2014  |  26  |
+|winter 2010  |  37|
-| Germany  |  winter 2014  |  19  |
+|winter 2010  |  23|
+|winter 2010  |  30|
+|winter 2014  |  25|
+|winter 2014  |  28|
+|winter 2014  |  26|
+|winter 2014  |  19|
-**See also**
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t//1279/4|Example: Constructing JSON]] ([[https://community.easymorph.com/uploads/short-url/m063pNiVTAgev8a7h7z93mlXbAU.morph|Project]]; Module: //Table2json//; Group: //Tab 1//; Table: //Create JSON elements//; Action position: //4//)
+  * [[https://community.easymorph.com/t//1482/6|Pivot tables basis data]] ([[https://community.easymorph.com/uploads/short-url/dkfcbulAExEaMObhjxprjxXdqtE.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //3//)
+  * [[https://community.easymorph.com/t//1778/2|Aggregate for +500 columns]] ([[https://community.easymorph.com/uploads/short-url/8w0FN8IXxRdEIORedS9u8DKqhfA.morph|Project]]; Module: //Main//; Group: //Order preserved//; Table: //Book1.xlsx(2)//; Action position: //2//)
+  * [[https://community.easymorph.com/t//2017/3|Duplicating rows based on a condition]] ([[https://community.easymorph.com/uploads/short-url/blETeOO6qUVzskyO2eYqMiNvjPP.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1 (2)//; Action position: //2//)
+  * [[https://community.easymorph.com/t//2421/8|“Merge another table” action - merge all columns (independently of the column name)]] ([[https://community.easymorph.com/uploads/short-url/jHsMLkfW9YZVmGYNYtayhOt1VkJ.morph|Project]]; Module: //Merge//; Group: //Merge//;\\ Table: //Type + Range//; Action position: //2//)
+\\
+=====See also=====
   * [[transformations:pivot|Pivot]]