WITH cteCombined AS (
select ROW_NUMBER() OVER (ORDER BY naziv) row_num,
naziv as [Naziv], jedinica_mjere [J.M.], sum(kolicina) as [Kolicina], '' as [Izlaz] from dbo.popis_repromaterijal_roba
where (select redni_broj from dbo.popis_repromaterijal_lista where redni_broj IS NOT NULL AND id = id_fakture) IS NOT NULL
group by sifra, naziv, jedinica_mjere
),
cteCombined2 AS (
select ROW_NUMBER() OVER (ORDER BY roba) row_num,
'' as [Naziv], '' as [J.M.], '0' as [Kolicina], roba as [Izlaz] from dbo.mp_racun_roba
where id_fakture IN (select id from mp_racun_lista where datum = '2023-10-01' )
AND roba=(select naziv from dbo.roba_usluge where podgrupa_artikala='HRANA' and roba_usluge.naziv=mp_racun_roba.roba)
group by sifra, roba
),
cte AS (
SELECT
row_num,
[Naziv],
[J.M.],
SUM([Kolicina]) AS [Kolicina],
[Izlaz]
FROM cteCombined
where [Kolicina] > 0
GROUP BY
row_num,
[Naziv],
[J.M.],
[Izlaz]
),
cte2 AS (
SELECT
row_num,
[Naziv],
[J.M.],
[Kolicina],
[Izlaz]
FROM cteCombined2
GROUP BY
row_num,
[Naziv],
[J.M.],
[Izlaz],
[Kolicina]
)
SELECT
row_num,
[Naziv],
[J.M.],
[Kolicina],
[Izlaz]
FROM cte
GROUP BY
row_num,
[Naziv],
[J.M.],
[Kolicina],
[Izlaz]
UNION ALL
SELECT
row_num,
[Naziv],
[J.M.],
[Kolicina],
[Izlaz]
FROM cte2
GROUP BY
row_num,
[Naziv],
[J.M.],
[Kolicina],
[Izlaz]
ORDER BY row_num
Result
row_num Naziv J.M. Kolicina Izlaz
1 AJVAR Kilogram 10.5300
1 0.0000 SIS CEVAP
2 0.0000 SREDNJI CEVAP
2 BIBER Kilogram 0.5000
3 BIJELI Kilogram 2.0000
3 0.0000 VELIKI CEVAP
4 BRAŠNO Kilogram 94.9000
5 MESO Kilogram 98.4500
Need To be

Need to be one row number
Example first row
1 AJVAR KILOGRAM 10.5300 SIS CEVAP
2 BIBER KILOGRAM 0.5000 SREDNJI CEVAP
ETC..