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