Azure SQL Database query ideas - Creating new columns in report from row data in table
Thereby improving readability of information
Table of contents
This post is part of the blog series created primarily for database engineers, with the hope that it might help you get a head start on your development tasks. With that being said, let us dive in...
Scenario
Consider that you have a table that records the execution details of various stored procedures (process name, start time, end time, execution status, etc.). A new change is being implemented wherein updated versions of certain stored procedures are developed and executed along with the original versions. We need to track the performance of the updated versions and compare those with the original versions. Format of the table is as shown below:
| PROCESS | DATE | START_TIME | END_TIME |
| ORDER_SUMMARY | 2021-02-01 | 2021-02-01 11:05:59.0000000 | 2021-02-01 11:14:59.2033333 |
| ORDER_SUMMARY_V2 | 2021-02-01 | 2021-02-01 12:05:59.0000000 | 2021-02-01 12:13:00.2033333 |
| ITEM_SUMMARY | 2021-02-01 | 2021-02-01 13:05:59.0000000 | 2021-02-01 13:10:00.2033333 |
| ITEM_SUMMARY_V2 | 2021-02-01 | 2021-02-01 14:05:59.0000000 | 2021-02-01 13:09:00.2033333 |
The output format of the report should be like this:
| Date | Process Name | V1 Duration in Seconds | V2 Duration in Seconds |
| 2021-02-01 | ORDER_SUMMARY | 599 | 496 |
| 2021-02-01 | ITEM_SUMMARY | 499 | 396 |
Building blocks of the solution
The Date is the only column that needs no transformation.
For Process Name, we need a case statement that checks if the value of PROCESS column is either ORDER_SUMMARY or ORDER_SUMMARY_V2, then displays ORDER_SUMMARY in the report. Similarly, if the value of PROCESS column is either ITEM_SUMMARY or ITEM_SUMMARY_V2, then displays ITEM_SUMMARY in the report. To make things simple, we assume a filter criteria that filters the query results for PROCESS in ('ORDER_SUMMARY', 'ORDER_SUMMARY_v2', 'ITEM_SUMMARY', 'ITEM_SUMMARY_v2')
For Duration in seconds, we use the DATEDIFF function passing as arguments START_TIME AND END_TIME.
Now coming to the key topic of this blog, converting rows data to columns. One way of achieving this is by using the case statement. When the process name does not contain "v2" display value of duration in the report column "V1 Duration in Seconds". When the process name contains "v2" display value of duration in the column "V2 Duration in Seconds". This will display the results as shown below.
| Date | Process Name | V1 Duration in Seconds | V2 Duration in Seconds |
| 2021-02-01 | ORDER_SUMMARY | 599 | NULL |
| 2021-02-01 | ORDER_SUMMARY | NULL | 496 |
| 2021-02-01 | ITEM_SUMMARY | 499 | NULL |
| 2021-02-01 | ITEM_SUMMARY | NULL | 396 |
Finally we can group this data by Date and Process Name, to get the Max(Duration) to get rid of NULL values and get just one row per Date and Process Name.
Final Solution
This is how the consolidated query looks like:
select
date,
case when (process='ORDER_SUMMARY' or process='ORDER_SUMMARY_v2')
then 'ORDER_SUMMARY'
when (process='ITEM_SUMMARY' or process='ITEM_SUMMARY_v2')
then 'ITEM_SUMMARY'
end as [Process Name],
max(case when process not like '%v2' then datediff(s,start_time,end_time) end) as [V1 Duration in Seconds],
max(case when process like '%v2' then datediff(s,start_time,end_time) end) as [V2 Duration in Seconds]
from [process_execution_status]
where process in ('ORDER_SUMMARY', 'ORDER_SUMMARY_v2', 'ITEM_SUMMARY', 'ITEM_SUMMARY_v2')
group by
date,
case when (process='ORDER_SUMMARY' or process='ORDER_SUMMARY_v2')
then 'ORDER_SUMMARY'
when (process='ITEM_SUMMARY' or process='ITEM_SUMMARY_v2')
then 'ITEM_SUMMARY'
end
order by 1,2
The output of this query should give us the desired report:
| Date | Process Name | V1 Duration in Seconds | V2 Duration in Seconds |
| 2021-02-01 | ORDER_SUMMARY | 599 | 496 |
| 2021-02-01 | ITEM_SUMMARY | 499 | 396 |
In the series of posts titled "Azure SQL Database query ideas", I will cover some of the topics that I usually encounter as part of my projects.