Transpose In-DB Tool Icon

Transpose In-DB Tool

Last modified: June 12, 2020

Laboratory Tool

This is a Laboratory tool and is not for production use. It may have documented known issues, may not be feature complete, and is subject to change.

Use Transpose In-DB to pivot the orientation of a data table in an In-DB workflow. It transforms the data so you can view horizontal data fields on a vertical axis.

This tool is useful for extracting non-conforming data. There is no limit to the amount of records or fields that can be transposed.

Support Limitations

Transpose In-DB supports only 64-bit SQL Server and Oracle data sources. It does not support spatial or blob fields.

In-Database enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. For more about the In-Database tool category, see In-Database Overview.

Configure the Tool

From Key Fields, select the fields to pivot the table around. This field Name remains on the Horizontal axis, with its value replicated vertically for each data field selected (step2).

The Key Field you select replicates vertically and there is a record for each selected data field to transpose. For example, if you are starting with a table that contains 477 records and has 100 fields, when you transpose that table using a Key field, you wind up with 477,000 records, and 3 fields (Key, Name of Field, and Value of that field).

From Data Fields, select all the fields to carry through the analysis.

Options on the right hand side for both selecting Key and Data Fields allow you to select All or Clear all selections.

Standard Versus In-DB

The Value field data type is determined in-database based on the selected Field data types. Therefore, the resulting Value string may appear differently than with the standard Transpose tool. This is particularly applicable when Date Time or Double fields are converted to strings. For example, a date value of 2007-12-30 01:01:01 in the standard Transpose tool could appear as Dec 30 2007 1:01AM in the Transpose In-DB tool. A double value of -2922678.4 in the standard Transpose tool could appear as -2.92268e+006 in the Transpose In-DB tool.

Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.