I work on sql server 2019 i face issue i need to give unique number to every group of numbers without using string aggreagte or stuff
original table as below :
create table #parts ( PartNumber varchar(50), PartValue int, UniqueNumber int ) insert into #parts(PartNumber,PartValue,UniqueNumber) values ('P1',1,NULL), ('P1',2,NULL), ('P1',3,NULL), ('P1',4,NULL), ('P2',1,NULL), ('P2',2,NULL), ('P3',1,NULL), ('P3',2,NULL), ('P3',3,NULL), ('P4',1,NULL), ('P4',2,NULL), ('P4',3,NULL), ('P5',1,NULL), ('P5',2,NULL)
expected result as below
PartNumber PartValue UniqueNumber P1 1 1 P1 2 1 P1 3 1 P1 4 1 P2 1 2 P2 2 2 P3 1 3 P3 2 3 P3 3 3 P4 1 3 P4 2 3 P4 3 3 P5 1 2 P5 2 2
what i try is
SELECT p.PartNumber, p.PartValue, p2.Parts, NewUniqueNumber = DENSE_RANK() OVER (ORDER BY p2.Parts) FROM #parts p JOIN ( SELECT p2.PartNumber, STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) Parts FROM #parts p2 GROUP BY p2.PartNumber ) p2 ON p2.PartNumber = p.PartNumber;
it give me expected result but i don't need to use this logic
are there are another logic without using string aggregate or stuff
i need to use another logic depend on sum numbers or count it