Multipurpose Apply Operator

One of major features that I've been fascinated by in SQL Server 2005 has been the APPLY operator. This article discusses on some of the real time scenarios where you can make use of this feature.

Scenario 1 - Running Total of a field

Consider the case where you need a running total to be calculated for a particular field. Here you can use the apply operator to calculate running total and return with each row.See below for illustration

declare @tbl table

(

Product_ID int identity(1,1),

Product_Name varchar(100)

)

insert @tbl (Product_Name)

select 'Boots' union all

select 'Peter England' union all

select 'Carona' union all

select 'Marks & Spencers'

declare @tbl_items table

(

Item_ID int identity(1,1),

Product_ID int,

Item_Date datetime,

Price numeric(10,2)

)

insert into @tbl_items (Product_ID,Item_date,Price)

select 1,'20 dec 2009',150 union all

select 2,'13 Apr 2009',908 union all

select 1,'5 Mar 2009',1974 union all

select 3,'05 jan 2010',647 union all

select 2,'13 Jun 2009',832 union all

select 3,'19 May 2008',413 union all

select 3,'13 May 2009',235 union all

select 1,'23 Oct 2009',630 union all

select 1,'30 Aug 2009',110

select Product_Name,Item_date,Price, Total

from @tbl t

join @tbl_items ti

on ti.Product_ID=t.Product_ID

cross apply (select sum(Price) as Total

from @tbl_items

where Product_ID=ti.Product_ID

and Item_date<=ti.Item_date) t1

Order by t.Product_ID,Item_Date

Scenario 2 - Get most recent or most ancient related data in case of 1-to -n relationship

Consider the case where you have one to many relationship existing between two tables and you want latest/earliest related detail from second table for every row of first table. In this case of you can use apply operator to achieve the result.

As an example using sample data from top, you can use below query to get latest sale detail of each product

select Product_Name,Item_date,Price

from @tbl t

cross apply (select top 1 Item_date,Price

from @tbl_items

where Product_ID=t.Product_ID

Order by Item_date desc) t1

Order by t.Product_ID

Result

Scenario 3 - Get concatenated string list of related field

Consider the case where you need to return a comma separated value of field which is related to master table field. Here also you can use apply operator coupled with for xml path to get the related values as csv list.

DECLARE @Student table

(

Student_ID int identity(1,1),

Student_name varchar(100)

)

INSERT @Student (Student_Name)

SELECT 'Ram' union all

SELECT 'Sita' union all

SELECT 'Murali' union all

SELECT 'Siddharth' union all

SELECT 'Jacob'

declare @Subjects table

(

Subject_ID int identity(1,1),

Subject_Name varchar(100)

)

INSERT @Subjects (Subject_Name)

SELECT 'Maths' union all

SELECT 'English' union all

SELECT 'Biology' union all

SELECT 'Physics' union all

SELECT 'Chemistry' union all

SELECT 'Hindi'

DECLARE @Student_subjects table

(

ID int identity(1,1),

Student_ID int,

Subject_ID int

)

INSERT INTO @Student_Subjects (Student_ID,Subject_ID)

SELECT 1,1 union all

SELECT 3,2 union all

SELECT 4,1 union all

SELECT 4,3 union all

SELECT 4,5 union all

SELECT 2,1 union all

SELECT 3,1 union all

SELECT 2,3 union all

SELECT 1,3 union all

SELECT 2,4 union all

SELECT 5,3

SELECT s.Student_Name,left(t.u,len(t.u)-1)

FROM @Student s

cross apply (select sub.Subject_name + ','

from @Subjects sub

inner join @Student_subjects ss

on ss.Subject_ID=sub.Subject_ID

where Student_ID=s.Student_ID

for xml path(''))t(u)

Scenario 4 - Call a table valued UDF passing each row value to do some processing

Consider the case where you need to parse a comma separated value in a field stored in a table field. Here also you can use apply operator to pass values to udf and return the individual values delimited by comma for each row of table.