3. The „Cross tabulate“ command in ACL™ does not have an as convenient equivalent in SQL
In the ACL™ script which I ported to SQL, the CROSSTAB command is used aggregate data similar to a Pivot table. This command allows multiple fields to be selected as criteria describing a row, and one field to be selected as aggregated column criteria.
To implement the same (or even similar) aggregation in SQL turned out to be tricky. I had to use dynamic SQL to achieve a comparable result.
Let’s have a look at an example:
We want to get the total amount for each customer in one line, differentiated by document type. Each result column name should be enhanced by the document type to provide a clear result structure and naming convention:
DocumentType_X and Amount_X and Quantity_X
X is a placeholder and represents the document type A, B or C.
The result should look as follows:
In ACL™ this can be done easily using the CROSSTAB command, which is only one line of ACL™code:
CROSSTAB ON Customer COLUMNS DocumentType Subtotal Amount Quantity to tCustomer_DocumentType_Crosstab COUNT OPEN
The equivalent to ACL™s CROSSTAB is the PIVOT function in SQL, however this requires some more work to reach the same result. In a first step I need to create appropriate column headers, whereto I need to attach the values in a second step.
The following code shows how I predefined the column headers and added the corresponding document types to it; then I assigned the this to a variable named @cols. The result is a single string containing the required column names. Should additional document types be added, then then this has to be taken into account in the query as well. Therefore, a “dynamic query” must be chosen rather than a static one.
Select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(DocumentType as varchar(4)))
select 'c_DocumentType, 0 union all
select 'c_Amount, 1 union all
select ‘c_Quantity,1 union all
) c (col, so)
group by col, so, DocumentType
order by DocumentType, so
FOR XML PATH(''), TYPE
The string contains the following elements:
[c_DocumentType_A],[c_Amount_A],[c_Quantity_A],[c_ DocumentType _B],[c_ Amount _B],[c_ Quantity _B],[c_ DocumentType _C], [c_ Amount _C],[c_ Quantity _C]
There is a set of columns now for each document type, with the document type value attached to the column name.
In a second step I have to take care of the aggregated values like total amount and total quantity. The values are retrieved from the prepared BaseTable and then assigned to the columns. The content of the @cols variable is passed on at the beginning of the SELECT statement so that the required columns are created, plus at the end of the query in the IN statement where the values are assigned to the respective columns. Because the query is stored in a variable as a string, to execute it the command "exec sp_executesql" is needed; by doing so, the executable string gets passed on to the stored procedure and executed accordingly.
= 'SELECT Customer, ' + @cols + N'
new_col = c.orig_col + ''_'' + cast(d.DocumentType as varchar(2)),
select Customer , DocumentType,
c_DocumentType = cast(c_DocumentType as varchar(50)),
c_Amount = cast(sum(c_Amount) over(partition by Customer, DocumentType) as varchar(50)),
c_Quantity = cast(sum(c_Quantity) over(partition by Customer, DocumentType)as varchar(59))
select ''c_DocumentType, d.c_ DocumentType union all
select ''c_Amount, d.c_ Amount union all
select ''c_Quantity, d.c_ Quantity union all
) c (orig_col, value)
for new_col in (' + @cols + N')
exec sp_executesql @query;
After the command was transmitted we obtain the desired result as Pivottable in SQL: