Goran Bibic

Goran Bibic

  • 483
  • 2.9k
  • 199.4k

Group By Row Number

Oct 22 2023 3:15 PM
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..


Answers (1)