private string BuildAxisAggregate(List <CustomLine> lines, IQueryAxis axis) { var syntaxHelper = new MicrosoftQuerySyntaxHelper(); var countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction); string countSqlWithoutAlias; string countAlias; syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias); CustomLine axisColumn; string axisColumnWithoutAlias; CustomLine axisGroupBy; string axisColumnAlias; AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy); return(string.Format( @" {0} {1} SELECT {2} AS joinDt,dataset.{3} FROM @dateAxis axis LEFT JOIN ( {4} ) dataset ON dataset.{5} = {2} ORDER BY {2} " , string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)), GetDateAxisTableDeclaration(axis), GetDatePartOfColumn(axis.AxisIncrement, "axis.dt"), countAlias, //the entire query string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)), axisColumnAlias ).Trim()); }
private string GetPivotPart1(List <CustomLine> lines, IQueryAxis axis, out IQuerySyntaxHelper syntaxHelper, out string pivotAlias, out string countAlias, out string axisColumnAlias) { syntaxHelper = new MicrosoftQuerySyntaxHelper(); //find the pivot column e.g. 'hb_extract AS Healthboard' var pivotSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Pivot); //the LHS e.g. hb_extract string pivotSqlWithoutAlias; //the RHS e.g. Healthboard syntaxHelper.SplitLineIntoSelectSQLAndAlias(pivotSelectLine.Text, out pivotSqlWithoutAlias, out pivotAlias); //ensure it has an RHS if (string.IsNullOrWhiteSpace(pivotAlias)) { pivotAlias = syntaxHelper.GetRuntimeName(pivotSqlWithoutAlias); } var countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction); string countSqlWithoutAlias; syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias); CustomLine axisColumn; string axisColumnWithoutAlias; CustomLine axisGroupBy; //if there is an axis we don't want to pivot on values that are outside that axis restriction. if (axis != null) { AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy); } else { axisColumnAlias = null; axisColumnWithoutAlias = null; } //Part 1 is where we get all the unique values from the pivot column (after applying the WHERE logic) bool anyFilters = lines.Any(l => l.LocationToInsert == QueryComponent.WHERE); string orderBy = countSqlWithoutAlias + " desc"; var topXOrderByLine = lines.SingleOrDefault(l => l.LocationToInsert == QueryComponent.OrderBy && l.Role == CustomLineRole.TopX); if (topXOrderByLine != null) { orderBy = topXOrderByLine.Text; } string havingSqlIfAny = string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.Having).Select(l => l.Text)); string part1 = string.Format( @" /*DYNAMICALLY FETCH COLUMN VALUES FOR USE IN PIVOT*/ DECLARE @Columns as VARCHAR(MAX) {0} /*Get distinct values of the PIVOT Column if you have columns with values T and F and Z this will produce [T],[F],[Z] and you will end up with a pivot against these values*/ set @Columns = ( {1} ',' + QUOTENAME({2}) as [text()] {3} {4} {5} ( {2} IS NOT NULL and {2} <> '' {7}) group by {2} {8} order by {6} FOR XML PATH(''), root('MyString'),type ).value('/MyString[1]','varchar(max)') set @Columns = SUBSTRING(@Columns,2,LEN(@Columns)) DECLARE @FinalSelectList as VARCHAR(MAX) SET @FinalSelectList = {9} --Split up that pesky string in tsql which has the column names up into array elements again DECLARE @value varchar(8000) DECLARE @pos INT DECLARE @len INT set @pos = 0 set @len = 0 WHILE CHARINDEX('],', @Columns +',', @pos+1)>0 BEGIN set @len = CHARINDEX('],[', @Columns +'],[', @pos+1) - @pos set @value = SUBSTRING(@Columns, @pos+1, @len) --We are constructing a version that turns: '[fish],[lama]' into 'ISNULL([fish],0) as [fish], ISNULL([lama],0) as [lama]' SET @FinalSelectList = @FinalSelectList + ', ISNULL(' + @value + ',0) as ' + @value set @pos = CHARINDEX('],[', @Columns +'],[', @pos+@len) +1 END if LEFT(@FinalSelectList,1) = ',' SET @FinalSelectList = RIGHT(@FinalSelectList,LEN(@FinalSelectList)-1) ", //select SQL and parameter declarations string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SELECT)), pivotSqlWithoutAlias, //FROM and JOINs that are not to the calendar table string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.FROM || l.LocationToInsert == QueryComponent.JoinInfoJoin && l.Role != CustomLineRole.Axis)), string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.WHERE)), anyFilters ? "AND" : "WHERE", orderBy, axisColumnWithoutAlias == null ? "": "AND " + axisColumnWithoutAlias + " is not null", havingSqlIfAny, axis != null ? "'joinDt'":"''" ); return(part1); }