You are here: Tools > Tool Categories > Laboratory > Transpose In-DB

Transpose In-DB Tool

IMPORTANT! 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.

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

Configuration Properties

  1. From the Key Fields Section, 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).
  2. From the Data Fields Section, select all the fields to carry through the analysis.
  3. Options on the right hand side for both selecting Key and Data Fields allow you to select All, or Clear all selections.
  4. 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).

    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. And a double value of -2922678.4 in the standard Transpose tool could appear as -2.92268e+006 in the Transpose In-DB tool.