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..