LucidDbAppLib PIVOT COLUMNS TO ROWS

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 4 Examples 5 Source Code

Syntax

SELECT * FROM TABLE( 
  APPLIB.PIVOT_COLUMNS_TO_ROWS ( 
    CURSOR ( sql_query ) ) )

PIVOT_COLUMNS_TO_ROWS is a UDX, which must be invoked in the FROM clause of a SQL query.

Purpose

Takes a single row as input and pivots the row into a table with two character string columns. The first column contains the names of the columns in the input query, and the second column contains the corresponding column values, converted to string values. The number of rows in the output is equal to the number of columns in the input.

Parameters

• sql_query: sql query that specifies the input row that will be pivoted in the output.

Examples

SELECT * FROM TABLE( APPLIB.PIVOT_COLUMNS_TO_ROWS( 
  CURSOR( select * from MYTABLE ) ) )

For table MYTABLE:

COLUMN_A	COLUMN_B	COLUMN_C	COLUMN_D	COLUMN_E
1	abc	3.1416		the previous column contains null

RETURNS:

COL_NAME	COL_VALUE
COLUMN_A	1
COLUMN_B	abc
COLUMN_C	3.1416
COLUMN_D
COLUMN_E	the previous column contains null

Source Code

http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/cursor/PivotColumnsToRowsUdx.java