ahmed salah

ahmed salah

  • 1.1k
  • 507
  • 31.4k

How to give unique number to every different group of numbers ?

Jul 24 2022 10:36 PM

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


Answers (2)