Using the Pivot function

Aster

Using the Pivot function

General format of pivot...

SELECT * FROM pivot(

ON { table_name | view_name | ( query ) }

PARTITION BY col1[, col2, ...]

[ ORDER BY order_by_columns ]

PARTITIONS('col1'[, 'col2', ...])

ROWS('number_of_rows')

PIVOT_KEYS('key1', 'key2'[, ...])

PIVOT_COLUMN( 'pivot_column_name' )

METRICS('metric_col1', 'metric_col2'[, ...])

);

Question:

I need help understanding pitvot_key and pivot_column

The docs says the following "Either the 'ROWS' argument or the 'PIVOT_KEY' and the 'PIVOT_COLUMN' arguments

need to be specified. The 'ROWS' argument specifies the maximum number of rows in all

of the partitions. If the number of rows in a partition is smaller than this argument, then

NULLs will be added; while if it is larger, the rest of the rows are omitted. See example 1

below for detail."

I have a column with parameters (e.g. Temp, Preasure, Setting1, Setting 2...) each with a value.  I need to go from

Temp, 123

Preasure, 43234.34

Setting 1, 'Blue'

To

Temp   Preasure     Setting 1

123    43234.34     Blue

Thanks for the help

Tags (3)
4 REPLIES
Enthusiast

Re: Using the Pivot function

Is there any Pivoy function available in Teradata or this syntax is for Oracle?

Enthusiast

Re: Using the Pivot function

(Sorry if this is a double or triple post.  Dang forum keeps rejecting my posts.)

Greg-

Have you considered writing your own SQL-MR function for this?  I know the built-in works just fine, but I find that if I'm doing something like pivoting rows, I probably have some other processing to do as well.  Luckily, writing an SQL-MR function that does pivoting is dead simple, and so tacking on the extra functionality isn't much of a bother.

By way of demonstration, I put a very simple example together in under ten minutes.  And most of that was correcting for typos.

The example assumes you want to normalize your output (1st normal form - eliminate repeating groups!) by emitting one row per input column with with "setting_" as the first 8 characters of its name.  The output will be all other columns, in order they are encountered, followed by an additional column named simple "setting."

i.e. an input record with "temp, pressure, setting_1, setting_2, setting_3" would emit three output rows per input row, with columns "temp, pressure, setting."

I hope this helps!

-Michael Cooper

----------code folows ("import" statements elided for the save of brevity)-----------------

public final class Quickpivot implements RowFunction {

    private int p_colcount = 0; //global var to hold count of columns

    private List<Integer> pivotColIndexes = new ArrayList<Integer>(); //global var to hold list of columns to pivot

    public Quickpivot(RuntimeContract contract) {

        List<ColumnDefinition> outputColumns = new ArrayList<ColumnDefinition>();

        //ADD ALL THE COLUMNS IN THE INPUT RECORD ***EXCEPT THOSE WHOSE NAMES START WITH "SETTING"***

        //AND CREATE A LIST OF COLUMN INDEXES THAT DO START WITH "SETTING"

        List<ColumnDefinition> inputColumns = contract.getInputInfo().getColumns();

        p_colcount=inputColumns.size();

        for(int i=0; i<p_colcount; i++) {

            ColumnDefinition columnDef = inputColumns.get(i);

            if(!columnDef.getColumnName().toLowerCase().matches("setting_[0-9]*"))

                outputColumns.add(columnDef);

            else

                pivotColIndexes.add(i);

        }

        //ADD AN OUTPUT COLUMN FOR THE PIVOTED "SETTING" COLUMN:

        outputColumns.add(new ColumnDefinition("Setting", SqlType.characterVarying()));

        contract.setOutputInfo(new OutputInfo(outputColumns));

        contract.complete();

    }

    public void operateOnSomeRows(RowIterator inputIterator, RowEmitter outputEmitter) {

        //For every input row

        while (inputIterator.advanceToNextRow()) {

            //For every column whose name starts with "Setting"

            for(int i=0; i<pivotColIndexes.size();i++) {

                //Compile a row with the un-pivoted columns first

                for(int j=0; j<p_colcount; j++) {

                    if(!pivotColIndexes.contains(j)) {

                        ValueHolder tmpVal = new ValueHolder(inputIterator.getColumnTypes().get(i));

                        inputIterator.getValueAt(j, tmpVal);

                        outputEmitter.addValue(tmpVal);

                    }

                }

                //Then add the pivoted column (remember, this happens for every column named like "Setting")

                outputEmitter.addString(inputIterator.getStringAt(pivotColIndexes.get(i)));

                //Send a row back to the user.

                outputEmitter.emitRow();

            }

        }

    }

}

[IMPORTANT: Code is provided as a courtesy, in an "as-is" basis w/o any promise that it will run properly.  Exercise extreme care, and run at your own risk.  No warranty is given or implied, and author shall not be held liable for any adverse consequences.]

Re: Using the Pivot function

Thanks for the thoughts.  We are building new SQL RM functions already.  I want to add common functionality to Aster

The Pivot function already knows the table and column that you are looking for.  This change would require accessing the table twice or working inside a temp table when you want to use this option.  The new Pivot would:

1. Get the unique list of values to make columns

2. Create the matrix of data

I believe this would be a simple change that would provide lots of power.  In the ever changing world I live in I need flexibility.  

Can tell me how to correctly code a TAYS for this I will, not sure of the category.

Enthusiast

Re: Using the Pivot function

Sorry - I should have made it clearer that I'm not a Teradata employee.

I've opened a few incidents in TAYS, but I'd recommend reaching out to your Teradata PS representative for help with that.

(And if that reply was meant to be for the general viewing audience, I apologize.  I just wanted to make sure nobody thought I was misrepresenting myself.)