<transpose>

<transpose> results in a table in which the rows from the original table are its columns and the columns from the original table are its rows.

Description

The <transpose> operation requires that all columns in the original table be the same data type. If the columns are not of the same type, set promote="1" to create a transposed table with all columns of the same type based on the following logic:
  • If a text column is present in the original table, all columns in the transposed table will be promoted to type text.
  • If no text columns are present in the original table and its columns are of mixed types (e.g., float and int), then all columns in the transposed table will be promoted to type float.
  • If all columns in the original table are of type int, then all columns in the transposed table will be of type int.

For more information on data types, see Data types.

Syntax

<transpose promote="[0|1]"
           names="[NEW_NAMES_COL]"
           labels="[NEW_LABELS_COL]"
           title="[TITLE]"
           sdesc="[SHORT_DESCRIPTION]"
           ldesc="[LONG_DESCRIPTION]"
           namecol="[ORIG_NAMES_COL]"
           labelcol="[ORIG_LABELS_COL]"
           typecol="[ORIG_TYPES_COL]"
           formatcol="[ORIG_FORMATS_COL]"/>

Attributes

promote
Specifies whether to promote columns from the original table so that all the columns in the transposed table have the same type. Accepts 0 or 1.

If columns in the original table are of mixed type, you must set promote="1".

The default is 0.

names
Specify the name of a column that contains unique, text-only, non-N/A values that are valid column names. These values become the column names in the transposed table.

If this attribute is not specified, the columns in the transposed table will be named m0, m1, m2, etc.

labels
Specify the name of a column that contains values for the column labels in the transposed table.

Unlike the names attribute, the values in the column provided to labels do not need to be unique values.

title
Specifies the title for the transposed table.
sdesc
Specifies the short description for the transposed table.
ldesc
Specifies the long description for the transposed table.
namecol
Specifies the name of a column in the transposed table that will contain the column names from the original table.
Set namecol="orig_names" to keep the original column names as row names.
labelcol
Specifies the name of a column in the transposed table that will contain the column labels from the original table.
Set labelcol="orig_labels" to keep the original column labels as row labels.
typecol
Specifies the name of a column in the transposed table that will contain the column types from the original table (such as i (integer), a (string), and f (decimal).
Set typecol="orig_types" to keep the original column types as row types.

For more information on data types, see Data types.

formatcol
Specifies the name of a column in the transposed table that will contain the display format strings from the original table.
Set formatcol="orig_formats" to keep the original column formats as row formats.

For more information on display formats, see Display formats.

Example: Transposing a simple table

In the following example, you have a simple table containing five columns and three rows. Note that the last two columns are labeled name and label. These will be the names and labels of the columns of the transposed table.

The following code transposes the above table:

<transpose promote="1" namecol="orig_names" 
             labelcol="orig_labels" typecol="orig_types"  
             names="name" labels="label"/>

namecol="orig_names", labelcol="orig_labels", and typecol="orig_types" provide columns with the original names, labels, and types of the columns before they were transposed. The names and labels attributes tell <transpose> where to find the names and labels of the columns to be used in the new, transposed, table.

The transposed table looks as follows:

The Names, Labels, and Types columns provide information about the column names, labels, and types in the original table. The fruits, vegetables, and grains are now arranged in columns instead of rows.

Example: Transposing a retail table

The following example demonstrates the results of performing a <transpose> operation on the table pub.demo.retail.store. The first two <willbe> operations set up the column names and column labels for the transposed table. The column new_column_names will contain values of the form col_n, where n is the row number from the original table. The column new_column_labels will contain values of the form "Column n", where n is the row number from the original table. Since promote="1", the columns in the transposed table will all be of type text, since the original table contains columns of type text (e.g., addr, city).

<base table="pub.demo.retail.store"/>
<willbe name="new_column_names" value="splice('col' i_;'_')"/>
<willbe name="new_column_labels" value="splice('Column' i_;' ')"/>
<transpose promote="1" 
 names="new_column_names" labels="new_column_labels" 
 namecol="orig_names" typecol="orig_types"/>