public virtual void Pivot2() { PivotTable pivot = new PivotTable(); SetupPivot(pivot); pivot.BaseSql = "select o.*, c.name, p.name productName from orders o inner join customers c on o.customerId = c.customerId inner join products p on o.productId = p.productId"; pivot.Function = SqlAggregationFunction.Count; pivot.ValueField = "productName"; pivot.RowField = "productName"; PivotColumn pivotCol = new PivotColumn("name", SqlDataType.String); pivotCol.Values.Add(PivotColumnValue.CreateScalar("c1", "John")); pivotCol.Values.Add(PivotColumnValue.CreateScalar("c2", "Lucinda")); pivotCol.Values.Add(PivotColumnValue.CreateScalar("c3", "Mark")); pivot.Columns.Add(pivotCol); pivot.WithTotal = false; SelectQuery pivotQuery = pivot.BuildPivotSql(); DataTable data = Fill(pivotQuery); if (data == null) { return; } WriteTable(data); }
public virtual void Pivot1() { SelectQuery baseQuery = new SelectQuery(); baseQuery.Columns.Add(new SelectColumn("*")); baseQuery.FromClause.BaseTable = FromTerm.Table("orders"); PivotTable pivot = new PivotTable(); SetupPivot(pivot); pivot.BaseQuery = baseQuery; pivot.Function = SqlAggregationFunction.Sum; pivot.ValueField = "quantaty"; pivot.RowField = "customerId"; PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date); TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year); pivotCol.Values.Add(PivotColumnValue.CreateRange("PreviousYears", new Range(null, currentYear.Add(-1).PeriodStartDate))); pivotCol.Values.Add(PivotColumnValue.CreateRange("LastYear", new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate))); pivotCol.Values.Add(PivotColumnValue.CreateRange("FollowingYears", new Range(currentYear.PeriodStartDate, null))); pivot.Columns.Add(pivotCol); pivotCol = new PivotColumn("productId", SqlDataType.Number); pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1)); pivotCol.Values.Add(PivotColumnValue.CreateRange("product2", new Range(2, 3))); pivot.Columns.Add(pivotCol); SelectQuery pivotQuery = pivot.BuildPivotSql(); DataTable data = Fill(pivotQuery); if (data == null) { return; } WriteTable(data); Console.WriteLine("Drill down"); SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "LastYear"); data = Fill(drillDownQuery); if (data == null) { return; } WriteTable(data); Console.WriteLine("Drill down"); drillDownQuery = pivot.BuildDrillDownSql(null, "LastYear"); data = Fill(drillDownQuery); if (data == null) { return; } WriteTable(data); }