Horizontal Aggregtions in SQL to Prepare Datasets for Data Mining Analysis

Abstract: Preparing a data set for analysis is the most time consuming task in a data mining project, requiring many complex SQL queries, joining tables, and aggregating columns. Existing SQL aggregations have limitations to prepare data sets because they return one column per aggregated group, and a significant manual effort is required to build data sets. In this paper proposing simple, efficient methods make SQL code return multiple columns in horizontal aggregation tables. It will return set of values instead of one value for one aggregation query. These functions of class are called as horizontal aggregations. It build data sets with a horizontal de normalized layout, which is the standard layout required by most data mining algorithms. In order to transform the data into suitable form three fundamental horizontal aggregation methods are used: SPJ (select, project, and join) method, CASE method and PIVOT method. The analysis become more efficient if the dataset obtained is in the horizontal form. Keywords: SQL Operators, Aggregate functions, Data Set Preparation.