Example of horizontally pivoting data (DataStage)

Example of horizontally pivoting data (DataStage®)

In this example, the Pivot Enterprise stage is set up to horizontally pivot some data.

You can generate a pivot index that will assign an index number to each row within sets of horizontally pivoted data. The following tables provide examples of data before and after a horizontal pivot operation.

Table 1. Input data for a simple horizontal pivot operation
REPID	last_name	Jan_sales	Feb_sales	Mar_sales
100	Smith	1234.08	1456.80	1578.00
101	Yamada	1245.20	1765.00	1934.22

Table 2. Output data for a simple horizontal pivot operation
REPID	last_name	Q1sales	Pivot_index
100	Smith	1234.08	0
100	Smith	1456.80	1
100	Smith	1578.00	2
101	Yamada	1245.20	0
101	Yamada	1765.00	1
101	Yamada	1934.22	2

Table 3. Input data for a horizontal pivot operation with multiple pivot columns
REPID	last_name	Q1sales	Q2sales	Q3sales	Q4sales
100	Smith	4268.88	5023.90	4321.99	5077.63
101	Yamada	4944.42	5111.88	4500.67	4833.22

Table 4. Output data for a horizontal pivot operation with multiple pivot columns
REPID	last_name	halfyear1	halfyear2
100	Smith	4268.88	4321.99
100	Smith	5023.90	5077.63
101	Yamada	4944.42	4500.67
101	Yamada	5111.88	4833.22

The following screen capture shows the settings for a horizontal pivot set by clicking Edit on the Pivot Properties section of the Stage tab.

Show the settings for a Horizontal pivot page configured for a horizontal pivot to generate a single column containing the horizontally pivoted data, and a column containing a generated pivot index — Figure 1. Example of horizontal pivot settings on the Horizontal pivot page.