dynamic pivot table query

[mk3]

Hello everyone,

When viewing a query report, there are times when one may wish to transpose rows into columns. One way is to copy paste it to a spread sheet and do the pivoting there. Another way is to create a pivot query in SQL.

There are two ways of creating a pivot query in SQL.

a) Standard/Static pivot table query

b) Dynamic pivot table query

a) Standard/Static pivot table query

In this type, the list of distinct values in a column to be pivoted are fixed.

The basic syntax is

Select * from

(your query here) t

Pivot

((aggregated column) FOR (pivoted column) IN ( [value 1],[value 2],..[value n])) as pvt

In the above standard/static pivot query you have to type the distinct values of the columns to be pivoted after the IN operator.

Hence, should a new value be added to the column to be pivoted, but not hard coded in the IN operator of the pivot operator query, then that value will not appear in the pivoted result. Because of this drawback, many prefer to just run normal query and do the pivot in a spreadsheet.

b) Dynamic pivot table query

This type overcomes the drawback of fixed columns of a standard pivot table query. In dynamic pivot table query the values of the column to be pivoted are not hard coded. But rather, the values of the column that has been pivoted expand/contract dynamically depending on the conditions chosen.

But this type is not commonly used,despite its advantages.It is susceptible to SQL injection attack. Hence, the standard recommendation is not to use it, but look for alternatives, eg run query and do the pivot in a spreadsheet.

SQL injection occurs when the attacker provides malicious data that will change the semantics of the intended SQL you are generating, affecting the way it will be interpreted in the system.

So, if the user is not allowed to input values at the time of execution, then your dynamic pivot table query is as safe as it can get from SQL injection attacks.

However, it is possible to mitigate the risks of SQL injection attacks, should a user have to input values into the dynamic pivot table query at time of execution.

Some of the ways in which we can mitigate the risks of SQL injection attacks are through use of:

1) QUOTENAME () function

2) Parameterized query

3) Use LIKE Operator Carefully

4) Code Review

5) Using certificates to sign stored procedures

 

The basic syntax for dynamic pivot table query using parameterization is

 

declare @cols nvarchar (max)= N”

DECLARE @query NVARCHAR(MAX)

select @cols = @cols + case when @cols = N” then QUOTENAME( name of column to be pivoted) else N’,’ + QUOTENAME( name of column to be pivoted) end

FROM    (SELECT DISTINCT (name of column to be pivoted) FROM table name) PV

ORDER BY name of column to be pivoted

SET @query = ‘SELECT * FROM

(your query here) t

Pivot

((aggregated column) FOR (name of column to be pivoted) IN ( ‘ + @cols + ‘)) as pvt’

EXEC SP_EXECUTESQL @query

 

1) QUOTENAME () function

The SQL Server QUOTENAME() function adds delimiters to an input string to make that string a valid SQL Server delimited identifier with default being square brackets. The case statement is used with QUOTENAME() function to remove the trailing/last comma from the string.

QUOTENAME() function on a value delimits the value with a character (for example – a single quote) which can also be specified in the function. So, even if the value contains some other characters like quotes or semicolons to inject harmful SQL statements, the value is delimited and those harmful SQL statements will now become part of the string to be searched in the table rather than valid SQL statements to be executed, and hence mitigate the risk of a sql injection attack. As the QUOTENAME () function returns the result as an Unicode string (note the prefix N), your code will work correctly for non standard identifier names too. The function is only appropriate for object names though e.g. table, column, database names.

Note: QUOTENAME () functions returns NULL, if the input string has more than 128 characters.

In our above Syntax,

We use a “FROM (SELECT DISTINCT (name of column to be pivoted) FROM table name” statement to select distinct values from the column to be pivoted and store them iteratively in the “@cols” variable.

The QUOTENAME () function will then put square brackets to the distinct values to be pivoted @Cols.

The statement SELECT @Cols = @Cols… iterates through the result and repeatedly concatenates the next location name to @Cols

hence, in our example 1 code below, it will become

[211301], [211401],[400101],[400102],[610104],[700101]

However, we cannot use a variable,@cols, inside our pivot operator. The alternative approach is to convert our complete SQL query to a string. Inside this string, we will hook our “@cols” variable.Here we declare a variable “@query” and store our SQL query in this variable. Inside the pivot operator, we concatenate the value stored inside the “@cols” variable by using the function ”  ‘ + @cols + ‘ "

2) Parameterized query

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time.

Parameterized query is a technique that aims to separate the SQL query from the user input values. The user input values are passed as parameters. They can no longer contain an executable code since the parameter is treated as a literal value and checked for the type and length.

Besides mitigating the risk of SQL injection attacks, a parameterized query improves the SQL command execution performance. When we use a parameterized query, SQL Server generates a query execution plan once and then it inserts the parameter value into it.

Parameterized queries can be used for any situation where untrusted input appears as data within the query, including the WHERE clause and values in an INSERT or UPDATE statement. They can't be used to handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause. Application functionality that places untrusted data into those parts of the query will need to take a different approach, such as white-listing permitted input values, or using different logic to deliver the required behavior.

Therefore, in our code example 1 below, the parameterized query is

WHERE T0.[TaxDate] between @fromdate and @Todate

Using parameterization gives you the advantage that you can clearly specify the data type and avoid pitfalls as well as the final T-SQL statement generated will reference the parameters as variables and not directly use the user defined input to generate the statement.

Working with parameters

EXEC SP_EXECUTESQL is an extended stored procedure that can be used to execute dynamic SQL statements in SQL Server.

To execute a parameterized query, we should pass two parameters to the extended stored procedure,EXEC SP_EXECUTESQL. The first must contain all the parameters names and data types (definition). The second one should contain all values

thus, in our example 1 code below, it is

N'@FROMDATE date, @TODATE date', @FROMDATE = @FROMDATE, @TODATE =@TODATE

Because the sp_executesql accepts the dynamic SQL as a Unicode string, you need to prefix the SQL command with an N.This denotes that the subsequent string is in Unicode (the N stands for National language character set). Which means that you are passing a NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

But be careful. Using sp_executesql is not a guarantee that the SQL statement to be executed is not susceptible to SQL injection. The parameters should be used properly in order to really take advantage of this feature.

While though not entirely foolproof for eliminating SQL Injection attacks, sp_executesql is likely to be the best you're going to get!

3) Use LIKE Operator Carefully

When user input is passed into a query that utilizes a LIKE operator, we need to be certain that the resulting query only requests valid data.

4) Code Review

This is a critical step of the software development process. The code should always be reviewed prior to testing and release

5) Using certificates to sign stored procedures


Save your dynamic pivot table query as a stored procedure.

You can use the MS SQL Server Management Studio, to create a stored procedure in the company database for the dynamic pivot table query.Check the link

https://answers.sap.com/questions/13712496/index.html

You can then restrict users rights to access regular tables by signing the stored procedure with a certificate.

 

And what about if we want to add row totals and column totals together with grand total to our pivot table that was generated dynamically. The answer is simple. You use the Group by extensions - Grouping Sets, Cube or Rollup. If you would like to know more about this, you may refer to my blog at

https://blogs.sap.com/2022/11/10/monthly-totals-and-grand-totals-using-grouping-sets-extension/

Example 1:

We will now look at an example of a dynamic pivot table query.

IF OBJECT_ID('tempdb.dbo.#temp1') IS NOT NULL DROP TABLE #temp1



Declare @fromdate as datetime

Declare @Todate as datetime

set @fromdate = /* select min(Ta.[fromDate]) from osrt ta 

where Ta.[fromDate] >= */ [%0]

set @Todate = /* select max(Tb.[toDate]) from osrt tb 

where Tb.[toDate] <= */ [%1]



declare @cols nvarchar (max)= N''

DECLARE @query NVARCHAR(MAX)



SELECT  left(datename(month,[date1]),3) +'   '+ datename(year,[date1]) 

as date1, CASE WHEN code  IS NULL THEN 'total' ELSE code END code,

CASE WHEN VATGroup IS  NULL THEN 'total1' ELSE VATGroup END VATGroup,

SUM(NetAmount) as Amount



INTO #TEMP1



FROM



(SELECT  T0.[TaxDate] AS 'Date1',T1.[TotalSumSy] as 'NetAmount', 

T1.[VatGroup] as 'VATGroup', T1.[AcctCode] as 'Code' 



FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 



WHERE T0.[TaxDate] between @fromdate and @Todate



UNION ALL



SELECT T0.[TaxDate], T1.[TotalSumSy]*-1, T1.[VatGroup],T1.[AcctCode]

 

FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 



WHERE T0.[TaxDate] between @fromdate and @Todate) U



GROUP BY Date1, CUBE (VATGroup,code)



select @cols = @cols + case when @cols = N'' then QUOTENAME (CODE) 

else N',' + QUOTENAME (CODE) end



FROM    (SELECT DISTINCT code FROM #TEMP1) PV  



ORDER BY code    ASC



SET @query = 'SELECT *



FROM ( SELECT * FROM #TEMP1 )  x



 PIVOT



( SUM(Amount)FOR [code] IN (' + @cols + ') ) p 



order by CONVERT(DATE,  CONVERT(varchar(11),DATE1,103),103) ,case when 

vatgroup=''total1'' then 5 else 3 end,vatgroup'



EXEC SP_EXECUTESQL @query , N'@FROMDATE date, @TODATE date', 

@FROMDATE = @FROMDATE, @TODATE =@TODATE

You will observe that in the above example that we are running the dynamic pivot table query on a temporary table. This is because executing a dynamic pivot query on a temporary table will only display the values of the pivoted column as per conditions chosen, and this is why it was used here.

Should one run a dynamic query directly on a regular table, then the query will pull ALL the distinct values of the column to be pivoted regardless of the conditions chosen. As an example, you can refer to

https://answers.sap.com/questions/9789720/dynamic-pivot---cannot-execute-in-query.html

This is not to say, that it is not possible to only display only those distinct values of a column to be pivoted as per conditions chosen when running the query directly on a regular table. An example of this can be found at

https://answers.sap.com/questions/13700717/dynamic-pivot-query-for-customer-ageing.html

But doing it this way is somewhat complex. You may have to do it this way though, if it is against your organization’s policy to use temporary tables.

Wait, you might ask, but aren’t the use of the statements like INSERT,CREATE,DROP etc other than the SELECT statement against SAP policy. That is correct, upto a certain extent You cannot use statements like INSERT,CREATE,DROP etc other than the SELECT statement on  System tables like oinv/inv1, opor/por1 etc. And neither can you use the statements like INSERT,CREATE,DROP etc other than the SELECT statement on User defined tables/User defined fields that have User defined objects attached.

If the UDT/UDF is "No object" or "No object with auto increment" and by extension, Temporary tables and Table variables, then statements like INSERT,CREATE,DROP etc including the SELECT statement can be used.

More information on this can be found at

https://blogs.sap.com/2016/07/28/guidelines-for-modifying-sap-business-one-data/

https://answers.sap.com/questions/13172573/sap-policy-on-use-of-createalter-drop-in-queries.html

Executing the above example query will give something like this:

date1	VATGroup	211301	211401	400101	400102	610104	700101	total
Mar 22	O1	10.00	8.00	100.00	70.00	55.00	60.00	303.00
Mar 22	X0	20.00	5.00	150.00	80.00	45.00	60.00	360.00
Mar 22	total1	30.00	13.00	250.00	150.00	100.00	120.00	663.00
Apr 22	O1	15.00	12.00	125.00	80.00	45.00	70.00	347.00
Apr 22	X0	25.00	4.00	125.00	80.00	60.00	75.00	369.00
Apr 22	total1	40.00	16.00	250.00	160.00	105.00	145.00	716.00

Here we have used the Group by extension, CUBE (VATGroup,code) to get the totals. But one could have equally used the group by extension, GROUPING SETS ((), (VATGroup), (code), (VATGroup,code)).

Exercise:

Replace CUBE (VATGroup,code) with

1. GROUPING SETS ((VATGroup,code) ,(code)) and see the result


2. GROUPING SETS ((VATGroup,code), (VATGroup)) and see the result

 

You will notice in the above result, that the [AcctCode] with alias “code” has been arranged in ascending order. You can also arrange it in descending order by specifying DESC in the parametrized variable select @cols

Example 2:

But are these the only two options that we have in arranging the pivoted columns? Supposing we want to order it differently say the 400* series first then the other columns. On top of that we may also want to put a blank column dynamically between the 400* series and the other series. Can we do this? The answer is yes. In order to do this what all we need to do is use like and not like operator in the parameterized variable, select @cols, as well add an additional line for the parametrized variable,select @cols, in order to get a blank column. See below:

IF OBJECT_ID('tempdb.dbo.#temp1') IS NOT NULL DROP TABLE #temp1



Declare @fromdate as datetime

Declare @Todate as datetime

set @fromdate = /* select min(Ta.[fromDate]) from osrt ta 

where Ta.[fromDate] >= */ [%0]

set @Todate = /* select max(Tb.[toDate]) from osrt tb 

where Tb.[toDate] <= */ [%1]



declare @cols nvarchar (max)= N''

DECLARE @query NVARCHAR(MAX)



SELECT  left(datename(month,[date1]),3) +'   '+ datename(year,[date1]) 

as date1, CASE WHEN code  IS NULL THEN 'total' ELSE code END code,

CASE WHEN VATGroup IS  NULL THEN 'total1' ELSE VATGroup END VATGroup,

SUM(NetAmount) as Amount 



INTO #TEMP1



FROM



(SELECT  T0.[TaxDate] AS 'Date1',T1.[TotalSumSy] as 'NetAmount', 

T1.[VatGroup] as 'VATGroup', T1.[AcctCode] as 'Code' 



FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 



WHERE T0.[TaxDate] between @fromdate and @Todate



UNION ALL



SELECT T0.[TaxDate], T1.[TotalSumSy]*-1, T1.[VatGroup],T1.[AcctCode] 



FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 



WHERE T0.[TaxDate] between @fromdate and @Todate) U



GROUP BY Date1, CUBE (VATGroup,code)





select @cols = @cols + case when @cols = N'' then QUOTENAME(code) else 

N',' + QUOTENAME(code) end 



FROM    (SELECT DISTINCT Code FROM #TEMP1) PV 



WHERE code LIKE '400%' 



select @cols += ',[T]'--ADDS BLANK COLUMN 



select @cols = @cols + case when @cols = N'' then QUOTENAME(code) else 

N',' + QUOTENAME(code) end 



FROM    (SELECT DISTINCT code FROM #TEMP1) PV 



WHERE code NOT LIKE '400%' 



ORDER BY code ASC



SET @query = 'SELECT * 



FROM ( SELECT * FROM #TEMP1 )  x



PIVOT



( SUM(Amount)FOR [code] IN (' + @cols + ') ) p 



order by CONVERT(DATE,  CONVERT(varchar(11),DATE1,103),103) ,case when 

vatgroup=''total1'' then 5 else 3 end,vatgroup'



EXEC SP_EXECUTESQL @query , N'@FROMDATE date, @TODATE date', 

@FROMDATE = @FROMDATE, @TODATE =@TODATE

The result will then look something like this:

date1	VATGroup	400101	400102	T	211301	211401	610104	700101	total
Mar 22	O1	100.00	70.00		10.00	8.00	55.00	60.00	303.00
Mar 22	X0	150.00	80.00		20.00	5.00	45.00	60.00	360.00
Mar 22	total1	250.00	150.00		30.00	13.00	100.00	120.00	663.00
Apr 22	O1	125.00	80.00		15.00	12.00	45.00	70.00	347.00
Apr 22	X0	125.00	80.00		25.00	4.00	60.00	75.00	369.00
Apr 22	total1	250.00	160.00		40.00	16.00	105.00	145.00	716.00

 

Summary:

A dynamic pivot table query is one in which a standard/static pivot table query has been encapsulated as a string.

Conclusion:

A dynamic query, just like any other computer programme, has an attack risk. One can but only mitigate the attack risks, as one does to any other computer programmes.

I hope that you found my blog informative. I will appreciate any feedback/comment that you may have.

With Regards,

Mk shah

References/Acknowledgements:

 

https://database.guide/introduction-to-temporary-tables-in-sql-server/

https://stackoverflow.com/questions/28227924/row-and-column-total-in-dynamic-pivot

https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

https://stackoverflow.com/questions/72060776/dynamic-pivot-with-parameter-passed-in

https://www.sqlshack.com/dynamic-sql-in-sql-server/

https://guptaashish.com/2014/05/05/sql-injection-dynamic-sql-with-in-clause-and-quotename/

https://www.c-sharpcorner.com/article/sql-injection-with-hex-code-and-its-prevention-mechanism/

https://www.sqlshack.com/using-parameterized-queries-to-avoid-sql-injection/

https://techcommunity.microsoft.com/t5/sql-server-blog/how-and-why-to-use-parameterized-queries/ba-p...

https://techcommunity.microsoft.com/t5/sql-server-blog/dynamic-sql-amp-sql-injection/ba-p/383196

https://www.sqlshack.com/sql-injection-detection-and-prevention/

https://www.sqlservercentral.com/articles/using-certificates-to-sign-stored-procedures

https://www.sommarskog.se/dynamic_sql.html