Differences

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

--- transformations:unpivot [2018/10/03 15:18] – dmitry
+++ transformations:unpivot [2025/01/17 01:18] (current) – revise to 2025 format 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 #1====
+>Convert the matrix table, below, into a standard data table. Unpivot all column except "Country".\\
-**Result:**
+===Before (source table)===
+Table 1: Winter Olympics medals
+^Country  ^Winter 2010  ^Winter 2014  ^
+| Canada  |  26|  25|
+| Norway  |  23|  26|
+| Germany  |  30|  19|
-^  Country  ^  Labels  ^  Data  ^
+===After (result table)===
-| Canada  |  winter 2010  |  26  |
+^Country  ^Labels  ^Data  ^
-| United States  |  winter 2010  |  37  |
+| **Canada**  |**Winter 2010**  |  **26**|
-| Norway  |  winter 2010  |  23  |
+| **Norway**  |**Winter 2010**  |  **23**|
-| Germany  |  winter 2010  |  30  |
+| **Germany**  |**Winter 2010**  |  **30**|
-| Canada  |  winter 2014  |  25  |
+| **Canada**  |**Winter 2014**  |  **25**|
-| United States  |  winter 2014  |  28  |
+| **Norway**  |**Winter 2014**  |  **26**|
-| Norway  |  winter 2014  |  26  |
+| **Germany**  |**Winter 2014**  |  **19**|
-| Germany  |  winter 2014  |  19  |
-**See also**
+===Action parameters===
+> Column name for labels: Labels
+> Column name for data: Data
+> Unpivot all columns except these: Country
+\\
+====Example #2====
+>Convert the matrix table, below, into a standard data table.\\
+===Before (source table)===
+Table 1: Winter Olympics medals
+^Country  ^Winter 2010  ^Winter 2014  ^
+| Canada  |  26|  25|
+| Norway  |  23|  26|
+| Germany  |  30|  19|
+===After (result table)===
+^Labels  ^Data  ^
+|**Country**  |**Canada**  |
+|**Country**  |**Norway**  |
+|**Country**  |**Germany**  |
+|**Winter 2010**  |  **26**|
+|**Winter 2010**  |  **37**|
+|**Winter 2010**  |  **30**|
+|**Winter 2014**  |  **25**|
+|**Winter 2014**  |  **28**|
+|**Winter 2014**  |  **19**|
+===Action parameters===
+> Column name for labels: Labels
+> Column name for data: Data
+\\
+=====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]]