<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
<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
or1
.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 tolabels
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.
labelcol
- Specifies the name of a column in the transposed table that will contain the column labels from the original table.
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), andf
(decimal). formatcol
- Specifies the name of a column in the transposed table that will contain the display format strings from the original table.
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"/>