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
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.
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) t1Order by t.Product_ID
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.
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)
I get this error when trying to run Scenario 4 code:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '.'.
are you using sql 2005? apply works only on sql 2005 and above.
Also your compatibility level should be 90 or above. check it first by using below query
EXEC sp_dbcmptlevel 'your database name'
and check if its 90 or above
This comment has been removed by a blog administrator.