Example: Aggregating Multiple Values from Multiple Rows Into One Instance - Advanced SQL Engine - Teradata Database

Use DATASET_PUBLISH to aggregate multiple values from multiple rows into one instance locally on each AMP within Teradata. This AMP-local aggregation is standard operating procedure for table operators.

DATASET_PUBLISH may be invoked twice within a statement to perform a single aggregation of all input values. If DATASET_PUBLISH is invoked once without the PARTITION BY clause, each AMP produces one row of aggregated output. Some input data is added to the source table in this example.

INSERT INTO employeeTable(4,'Jose Hernandez','Engineering'); 
INSERT INTO employeeTable(5,'Kyle Newman','Engineering');
INSERT INTO employeeTable(6,'Pamela Giles','Sales');

SELECT data.toJSON() FROM DATASET_PUBLISH
(
	ON (SELECT * FROM employeeTable)	 
) AS avroFiles;

data.toJSON()
[{
	"empID": 5,
	"empName": "Kyle Newman",
	"empDept": "Engineering"
},
{
	"empID": 3,
	"empName": "Steven Mazzo",
	"empDept": "Engineering"
},
{
	"empID": 1,
	"empName": "George Smith",
	"empDept": "Accounting"
},
{
	"empID": 2,
	"empName": "Pauline Kramer",
	"empDept": "HR"
}]
–-------------------------------------
[{
	"empID": 4,
	"empName": "Jose Hernandez",
	"empDept": "Engineering"
}]
–-------------------------------------
[{
	"empID": 6,
	"empName": "Pamela Giles",
	"empDept": "Sales"
}]