@yosiasz - I can't tell if you're being sincere or sarcastic. Since I can't tell, I'll explain and hope you were being sincere...
This particular user is known on this and several other forums for providing partial requirements and not answering questions.
Here's what the OP ultimately posted on the other forum as an additional explanation...
the goal mfrom [sic] 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
My question to everyone is, even with the example data AND the query he provided, would YOU have guessed that desired grouping is based on "both the same count and same sum" as the actual requirement? I think not. My impatience here is that he DID post that as a separate post on the other forum but he did not post it here. And, that requirement is also the reason why he doesn't want to use STRING_AGGREGATE()... it creates the wrong answer. And, none of the test data uses the 2,2,2 example he gave later and there's no way to even guess that from the original post.
And, finally, the OP never came back to this thread to say that he'd already gotten a solution and post the solution.
How can you have any patience with that? Because this OP is basically a "Good Man" at heart and a fellow "database warrior", we're trying to teach this good man some forum etiquette because a whole lot of people don't have such patience (and I've been PM'd a couple of times about it) and simply refuse to try to help him unless he changes his ways.
For those that are interested, you can find one solution at the following URL...
https://www.sqlservercentral.com/forums/topic/how-to-give-unique-number-to-every-different-group-of-numbers/page/2#post-4068160
To prevent any future "site rot", here's the test data I posted there...
--Add 'P6' to meet the requirements stated in
--https://www.sqlservercentral.com/forums/topic/how-to-give-unique-number-to-every-different-group-of-numbers#post-4067708
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),
('P6',2,NULL), --Added to test given requirements
('P6',2,NULL), --Added to test given requirements
('P6',2,NULL) --Added to test given requirements
;
And here's the solution I posted...
WITH
cteCounts AS
(
SELECT PartNumber
,PartValue
,C = COUNT(PartValue) OVER (PARTITION BY PartNumber)
,S = SUM (PartValue) OVER (PARTITION BY PartNumber)
FROM #parts
)
SELECT PartNumber
,PartValue
,UniqueNumber = DENSE_RANK() OVER (ORDER BY C,S)
FROM cteCounts
ORDER By PartNumber
;
And here are the results... Do notice how P6 is a part of Group 2 (Inique Number) because, like P3, it has a count of 3 and the 3 counts sum up to 6 for both groups.