Transpose In-DB 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.

The Transpose In-DB tool allows you to pivot the orientation of a data table in an In-DB workflow. It transforms the data so you may 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 field(s) to pivot the table around. This field Name will remain on the Horizontal axis, with its value replicated vertically for each data field selected (step2).

The Key Field you select will replicate vertically and there will be 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 will 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.