Click here to Skip to main content
15,880,796 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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 have tried:

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;
Posted
Updated 24-Jul-22 22:05pm
Comments
ahmed_sa 25-Jul-22 3:06am    
the goal mfrom asking question is to get result above without string aggreagte or comma separated
suppose i have
p1 1,2,3
p2 2,2,2
both p1 and p2 both have same count and same sum
so are there are another solution without
using string aggregate

1 solution

Try this:

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


SQL Server 2019 | db<>fiddle[^]

Note: Replace [*] with column names you want to get in subquery. I was lazy ;)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900