Intermediate SQL Functions

Real time work on sql some times demands functions beyond simple statements and these days my work is demanding sql functions beyond simple where clause, having clause or window functions. I will be discussing some of those functions in this blog.

Useful SQL functions

• Pivot
• Unpivot
• Correlated subqueries
• Values
• Cross Apply

I will be starting with pivot and move till cross apply with some of my use cases.

Pivot:

Use case: The data I was required to generate has some columns that are stored in rows of a table. So I need to keep other column values as it is and need to convert required row values into columns.

Table:

Data in the base table

Output data

This can be achieved easily by following 3 steps

• Use main table data in a subquery.
• Apply pivot function on the column values that are to be pivoted
• select required columns by querying this data

select id,customer_id,recipt,rating_report,tat_report 
from
(select * from table1)as tab1
pivot
(max(upload_status)for document in (recipt,rating_report,tat_report))as tab2

Here max can be replace by any aggregate function according to requirement.

Unpivot:

Usecase: I have a requirement to calculate bonus for each employee of my client company based on their role and job done table. I need to generate data from 2 tables where I need to map row values of 1st table with columns on 2nd table. Its impossible to do this directly and then there comes unpivot function to solve this.

Employee Job Done Table

Bonus multiplier based on role

My clients calculate bonus by multiplying employee’s parameter value with corresponding value in Bonus multiplier table based on role.

So only way to do this is to unpivot Bonus multiplier table and join this table with Employee Job Done Table.

This unpivoting can be achieved easily by following 3 steps

• Use main table data in a subquery.
• Apply unpivot function on the columns that are to be unpivoted
• select required columns by querying this data

** Unpivoting can also be done by cross apply which is discussed later in the blog***

Unpivoting:

select role,parameter,multiplier as value from
(select * from table1)as tab1
unpivot
(
multiplier for parameter in (cases_done, provide_new_clients  ,cases_under_tat)
) as unp_tab1

Result:

Unpivoted table

Now I joined Employee Job Done Table with Unpivoted table and calculated final bonus.

select e.employee_id,e.role,e.parameter,
e.value*u.value as bonus
from Employee_Job_Done e
join Unpivoted_table u on e.role=u.role and e.parameter=u.parameter

Final Bonus

Correlated subqueries:

We might face many situations where every row from table should be evaluated in a sub query. So here the context is row context and the subquery should run for every new row. These type of cases are referred as correlated subqueries.

Generally in nested sub-queries the inner select statement runs first and executes once, then returns values to be used by main query. But with correlated subqueries inner query runs once for every new record.

One classic example for this is finding the mileage of bikes that are greater than average mileage of that category.

Bikes vs Mileage

select * from bikes b_i
where mileage >= (select avg(mileage) from bikes b_o where b_i.category=b_o.category)

Result

Exists function is also an example for correlated subqueries. Here for ever new record in the main query it checks the existence of some column or a value in the sub-query and if it finds one inner query returns true. So for every new record inner query executes once.

Get Pavan Krishna’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Values function:

Classic example of using values function is insertion of values into a table but we can do way more than just table insertion.

It can be used for creating records dynamically based on the values of main query. Its usecase is mentioned below in “Unpivoting some columns of a table” section.

Cross Apply:

Cross Apply function is highly useful when we want to run or join every row of the main query with a sub query. In some ways cross apply is similar to inner join but it has many other use cases.

Use cases:

• Parameterized functions
• Top n records (n changes from record to record in the base table)
• Unpivoting some columns of a table
• Reuse calculated columns

We can use cross apply instead of joins in many cases to produce same results and execution plan will also be same for both unless indices or statistics are created.

Cross apply with Parameterized/table valued functions:

When we are working with some clients or a company they always have some data that is to be kept confidential. In these cases they provide some functions that provides some specific columns that satisfies our requirement.

Let us suppose there is a table with some details of customers along with some data that is to be analyzed. Clients can’t provide entire data of the table so they will create some functions that provides all columns other than customer related data.

Parameterized functions:

CREATE FUNCTION purchase(
    @branch_id INT
)
RETURNS TABLE
AS
RETURN
    SELECT
        branch_id,
        product_name,
        category,
        price,
        discount
    FROM
        dbo.orders
    WHERE
        branch_id= @branch_id;

This function hides all the customer details. Now we have a requirement of joining a table with this orders table.

If we join a table with this function we get multiparty identity error. The best way to solve this issue is to use cross apply.

Here for every row in the main query a branch_id will be passed as a parameter to the inner query.

Requirement: Map branch_id with orders table and calculate business done report (BDR) by each branch. So for every branch map branch_id with purchase function to get all details and use this output to calculate business done by each branch.

select b.*,p.price,p.category from dbo.dim_branch  as b
cross apply (
select dbo.purchase(a.branch_id) p
)

Use above output with group by branch and sum(price) to find the BDR.

Top n records (n changes from record to record):

Use case: Suppose we have customers table that has customer id, his lucky number or some other number(n). We are supposed to get the top n orders by the customer from orders table.

So one of the best way to do this is to pass this n to the inner query as a parameter. To solve this issue we can use cross apply.

select c.customer_id,c.n,o.product,o.price,o.purchase_date
from customers as c
cross apply (
select top(c.n) product,price,purchase_date
from orders
where c.customer_id=orders.c_id
order by purchase_date) as o

Here for every customer we are passing their lucky number to the orders table and getting top(n) data from purchase table.

There are other methods to do this like using row_number in inner query and comparing this row_number with lucky number in outer query. But by doing this we are adding many extra steps to the execution process. For these type of cases Cross Apply works the best. While using cross apply we are also reducing the number of comparisons in the inner query because when the top(n) is reached it won’t search for the next matching record which is not the case while using row_number.

Unpivoting some columns of a table:

Unpivoting is not only done by unpivot function but we can also use cross apply function to unpivot a table.

Consider the same example we used previously.

Bonus multiplier based on role

To unpivot cases_done , provide_new_clients , cases_under_tat:

select role ,bonus.parameter,bonus.value
from 
cross apply
(values((cases_done,'cases_done'),
        (provide_new_clients,'provide_new_clients'),
        (cases_under_tat,'cases_under_tat')) bonus(value,parameter)

Unpivoted table

Explanation:

Here values function inside the cross apply creates new dynamic records for every row in the main query. It produces parameter column that has cases_done , provide_new_clients , cases_under_tat and value column with respective values. Now it appends these records to record in main query.

Reuse calculated columns:

Use case: In some cases while calculating bonus of employees we are asked to provide bonus only for some roles in some department for next 3 months. We need to use case when statement repeatedly for 3 times for every record if we follow general approach.

General approach:

select emp_id,base_pay,role,department,
case when role='manager' and department='Sales' then base_pay*2 else 0 end as jan_bonus,
case when role='manager' and department='Sales' then base_pay*3 else 0 end as feb_bonus,
case when role='manager' and department='Sales' then base_pay*4 else 0 end as mar_bonus
from dbo.emp_master

Cross apply approach:

Using cross apply we will reduce the use of case when statement.

select emp_id,base_pay,role,department,
emp.bonus*2 as jan_bonus,
emp.bonus*3 as feb_bonus,
emp.bonus*4 as mar_bonus,
from dbo.emp_master
cross apply( select case when role='manager' and department='Sales' then base_pay else 0 end) as emp(bonus)

These are some of the functions we don’t use frequently but has a lot of value in it if certain use cases arrive.

If you found this article helpful. Do follow me on medium. Any type of constructive comments or feedback is encouraged.

Let’s get connected via Linkedin for any further discussions on big data and data engineering topics.