Hi @Analyst_SQL ,

LEAD is supported starting with SQL Server 2012 (11.x).

So you will receive "'lead' is not a recognized built-in function name." error if you use this function in SQL 2008.

Please refer below:

```
DROP TABLE IF EXISTS #item,#DistpatchM,#Distpatch_BD
CREATE TABLE #item (ItemCode int,ItemName varchar(max))
CREATE TABLE #DistpatchM(DID int,E_Date date)
CREATE TABLE #Distpatch_BD(ID int, Itemcode int, DID int, DisQTY int, Bweight int)
INSERT INTO #item VALUES(1001, 'A')
INSERT INTO #item VALUES(1002, 'B')
INSERT INTO #item VALUES(1003, 'C')
INSERT INTO #item VALUES(1004, 'D')
INSERT INTO #item VALUES(1005, 'E')
INSERT INTO #item VALUES(1006, 'F')
INSERT INTO #item VALUES(1007, 'G')
INSERT INTO #DistpatchM values(111,'2019-05-28')
INSERT INTO #DistpatchM values(112,'2018-05-29')
INSERT INTO #Distpatch_BD values(1,1001,111,1 ,500)
INSERT INTO #Distpatch_BD values(2,1002,111 ,1,600)
INSERT INTO #Distpatch_BD values(3,1003,111 ,1,800)
INSERT INTO #Distpatch_BD values(4,1006,112,1,900)
INSERT INTO #Distpatch_BD values(5,1006,112,1,4500)
INSERT INTO #Distpatch_BD values(6,1006,112,1,650)
INSERT INTO #Distpatch_BD values(7,1006,112,1,900)
INSERT INTO #Distpatch_BD values(8,1006,112,1,4500)
INSERT INTO #Distpatch_BD values(9,1006,112,1,650)
INSERT INTO #Distpatch_BD values(10,1006,112,1,900)
INSERT INTO #Distpatch_BD values(11,1006,112,1,4500)
INSERT INTO #Distpatch_BD values(12,1006,112,1,650)
INSERT INTO #Distpatch_BD values(13,1006,112,1,4500)
INSERT INTO #Distpatch_BD values(14,1006,112,1,650)
INSERT INTO #Distpatch_BD values(14,1001,112,1,400)
;with I as
(
select *
from #Distpatch_BD
where DID in (112)
),
N as
(
select *, row_number() over (partition by DID,ITEMCODE order by ID) - 1 as n
from I
),
P as
(
select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r
from N
) ,
G as
(
select p.*,
ISNULL(P0.BweightT,'') [1],
ISNULL(P1.BweightT,'') [2],
ISNULL(p2.BweightT,'') [3],
ISNULL(P3.BweightT,'') [4],
ISNULL(P4.BweightT,'') [5],
ISNULL(P5.BweightT,'') [6],
ISNULL(P6.BweightT,'') [7],
ISNULL(P7.BweightT,'') [8],
ISNULL(P8.BweightT,'') [9],
ISNULL(P9.BweightT,'') [10]
from p
left join p p0
on p.did=p0.did and p.itemcode=p0.itemcode and p.n=p0.n
left join p p1
on p.did=p1.did and p.itemcode=p1.itemcode and p.n=p1.n-1
left join p p2
on p.did=p2.did and p.itemcode=p2.itemcode and p.n=p2.n-2
left join p p3
on p.did=p3.did and p.itemcode=p3.itemcode and p.n=p3.n-3
left join p p4
on p.did=p4.did and p.itemcode=p4.itemcode and p.n=p4.n-4
left join p p5
on p.did=p5.did and p.itemcode=p5.itemcode and p.n=p5.n-5
left join p p6
on p.did=p6.did and p.itemcode=p6.itemcode and p.n=p6.n-6
left join p p7
on p.did=p7.did and p.itemcode=p7.itemcode and p.n=p7.n-7
left join p p8
on p.did=p8.did and p.itemcode=p8.itemcode and p.n=p8.n-8
left join p p9
on p.did=p9.did and p.itemcode=p9.itemcode and p.n=p9.n-9
)
,
R as
(
select *
from G
where c = 1
)
,
Max as
(
select ITEMCODE,max(r) max
from G
where c = 1
group by ITEMCODE
),
F as
(
select
case r when 1 then t.ItemName else '' end as Item,
case r when 1 then cast((select count(*) from I where ITEMCODE = R.ITEMCODE and did=r.did) as varchar(max)) else '' end as QTY,
case when r=max then
cast((select sum(Bweight) from I where ITEMCODE = R.ITEMCODE) as varchar(max))
else '' end
as Total,
R.*
from R
inner join #Item t on t.ITEMCODE = R.ITEMCODE
left join max m on m.ITEMCODE=r.ITEMCODE
),
GT as
(
select cast(count(*) as varchar(max)) as GrandTotalQTY,
cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight
from I
)
,
E as
(
select distinct DID
from I
),
U as
(
select DID, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2
from F
union all
select DID, '', '', '', '', '', '', '', '', '', '', '', '', '', 2, 1
from E
union all
select NULL, 'Grand Total', GrandTotalQTY, '', '', '', '', '', '', '', '', '', '', GrandTotalBWeight, 1, 2
from GT
)
select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total
from U
```

Output:

Best regards

Melissa

If the answer is helpful, please click "**Accept Answer**" and **upvote** it.

**Note: Please follow the steps in our [documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.**

1: https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15 [3]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html