SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE PP
(
  PartNumber varchar(50),
  PartValue int
);

INSERT INTO PP(PartNumber, PartValue)
VALUES
('P1',1),('P1',2),
('P1',3),('P1',4),
('P2',1),('P2',2),
('P3',1),('P3',2),
('P3',3),('P4',1),
('P4',2),('P4',3),
('P5',1),('P5',2);
14 rows affected

SELECT
   p.PartNumber,
   p.PartValue,
   p2.Parts,
   NewUniqueNumber = DENSE_RANK() OVER (ORDER BY p2.Parts)
 FROM PP p
 JOIN (
     SELECT
       p2.PartNumber,
       STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) Parts
     FROM PP p2
     GROUP BY
       p2.PartNumber
 ) p2 ON p2.PartNumber = p.PartNumber;


PartNumber
PartValue
Parts
NewUniqueNumber


P2
1
1,2
1

P2
2
1,2
1

P5
1
1,2
1

P5
2
1,2
1

P3
1
1,2,3
2

P3
2
1,2,3
2

P3
3
1,2,3
2

P4
1
1,2,3
2

P4
2
1,2,3
2

P4
3
1,2,3
2

P1
1
1,2,3,4
3

P1
2
1,2,3,4
3

P1
3
1,2,3,4
3

P1
4
1,2,3,4
3


SELECT T.PartNumber, T.PartValue,T.CNT, DENSE_RANK() OVER(ORDER BY T.CNT) AS UniqNo
FROM 
(
  SELECT *, COUNT(*) OVER(PARTITION BY PartNumber) AS CNT
  FROM PP
) AS T


PartNumber
PartValue
CNT
UniqNo


P2
1
2
1

P2
2
2
1

P5
1
2
1

P5
2
2
1

P3
1
3
2

P3
2
3
2

P3
3
3
2

P4
1
3
2

P4
2
3
2

P4
3
3
2

P1
1
4
3

P1
2
4
3

P1
3
4
3

P1
4
4
3

PartNumber	PartValue	Parts	NewUniqueNumber
P2	1	1,2	1
P2	2	1,2	1
P5	1	1,2	1
P5	2	1,2	1
P3	1	1,2,3	2
P3	2	1,2,3	2
P3	3	1,2,3	2
P4	1	1,2,3	2
P4	2	1,2,3	2
P4	3	1,2,3	2
P1	1	1,2,3,4	3
P1	2	1,2,3,4	3
P1	3	1,2,3,4	3
P1	4	1,2,3,4	3

PartNumber	PartValue	CNT	UniqNo
P2	1	2	1
P2	2	2	1
P5	1	2	1
P5	2	2	1
P3	1	3	2
P3	2	3	2
P3	3	3	2
P4	1	3	2
P4	2	3	2
P4	3	3	2
P1	1	4	3
P1	2	4	3
P1	3	4	3
P1	4	4	3

PartNumber	PartValue	CNT	UniqNo
P2	1	2	1
P2	2	2	1
P5	1	2	1
P5	2	2	1
P3	1	3	2
P3	2	3	2
P3	3	3	2
P4	1	3	2
P4	2	3	2
P4	3	3	2
P1	1	4	3
P1	2	4	3
P1	3	4	3
P1	4	4	3

PartNumber	PartValue	CNT	UniqNo
P2	1	2	1
P2	2	2	1
P5	1	2	1
P5	2	2	1
P3	1	3	2
P3	2	3	2
P3	3	3	2
P4	1	3	2
P4	2	3	2
P4	3	3	2
P1	1	4	3
P1	2	4	3
P1	3	4	3
P1	4	4	3