HI @Viorel-1 ,
as your Provided solution ,l little modified ,when i am executing it ,then it is not display all rows total ,
Note: You used DID instead of Codeitem,but i used Codeitem instead of DID
with I as
(
select *
from Dispatch_BD
where DID in (1195)
),
N as
(
select *, row_number() over (partition by codeitem 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 *,
isnull(lead(BweightT, 0) over (partition by Codeitem order by ID), '0') as [1],
isnull(lead(BweightT, 1) over (partition by Codeitem order by ID), '0') as [2],
isnull(lead(BweightT, 2) over (partition by Codeitem order by ID), '0') as [3],
isnull(lead(BweightT, 3) over (partition by Codeitem order by ID), '0') as [4],
isnull(lead(BweightT, 4) over (partition by Codeitem order by ID), '0') as [5],
isnull(lead(BweightT, 5) over (partition by Codeitem order by ID), '0') as [6],
isnull(lead(BweightT, 6) over (partition by Codeitem order by ID), '0') as [7],
isnull(lead(BweightT, 7) over (partition by Codeitem order by ID), '0') as [8],
isnull(lead(BweightT, 8) over (partition by Codeitem order by ID), '0') as [9],
isnull(lead(BweightT, 9) over (partition by Codeitem order by ID), '0') as [10]
from P
),
R as
(
select *
from G
where c = 1
),
F as
(
select
case r when 1 then t.Descriptionitem else '0' end as Item,
case r when 1 then cast((select count(*) from I where Codeitem = R.Codeitem) as varchar(max)) else '0' end as QTY,
case when lead(DID) over (partition by DID order by r) is null
then cast((select sum(Bweight) from I where CodeItem = R.CodeItem) as varchar(max)) else '0' end as Total,
R.*
from R
inner join ItemMasterFile t on t.CodeItem = R.Codeitem
),
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 codeitem
from I
),
U as
(
select codeitem, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2
from F
union all
select codeitem, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 2, 1
from E
union all
select NULL, 'Grand Total', GrandTotalQTY, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', GrandTotalBWeight, 1, 2
from GT
)
select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total
from U
order by ord2, codeitem, ord1

