Query to get maximum SUM based on another column value in SQL Server

Hi PRA,

Refer below query.

DECLARE @fruits AS TABLE (Id INT, name VARCHAR(10), price INT)
INSERT INTO @Fruits VALUES (1,'apple',5)
INSERT INTO @Fruits VALUES (2,'orange',4)
INSERT INTO @fruits VALUES (3,'onion',6)
INSERT INTO @Fruits VALUES (4,'tomato',9)
 
DECLARE @Person AS TABLE (Id INT, pname VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Firuz')
INSERT INTO @Person VALUES(3,'Nurullo')
 
DECLARE @Cash AS TABLE (Id INT,idperson int ,customername VARCHAR(10),idfruit VARCHAR(10), numberprice INT)
INSERT INTO @Cash VALUES(1,1,'Suhrob','1,3',1)
INSERT INTO @Cash VALUES(2,3,'Amonullo','2,3',2)
INSERT INTO @Cash VALUES(3,1,'Suhrob','3,1',3)
INSERT INTO @Cash VALUES(4,2,'Alijon','1,3',4)
INSERT INTO @Cash VALUES(6,2,'Amonullo','2,1,4',6)
 
DECLARE @IdPer INT
SET @IdPer = 2
SELECT NumId,
STUFF((SELECT ',' + NAME
        FROM(
            SELECT c.numberprice NumId,LTRIM(RTRIM(f.NAME))NAME
            FROM @fruits f, @cash c
            WHERE f.ID IN (SELECT CAST(item AS INTEGER) FROM dbo.SplitString(c.idfruit, ','))
            AND c.IdPerson = @IdPer
            ) as childTable
        WHERE  mainTable.NumId = childTable.NumId
        FOR XML PATH('')), 1, 1, ' ') AS Name,
customername,
pname,
(SELECT SUM(f.price) 
FROM (
	SELECT CAST(Item AS INTEGER) Item 
	FROM dbo.SplitString((SELECT TOP 1 idfruit 
						FROM @Cash 
						WHERE idperson = @IdPer 
						ORDER BY numberprice DESC), ',')
)t
INNER JOIN @fruits f ON f.Id = t.Item) Price
FROM(
    SELECT c.numberprice NumId,p.pname,c.customername,c.numberprice,f.price
    FROM @cash c  
	JOIN @person p ON c.idperson = p.id, @fruits f
    WHERE c.numberprice = (SELECT MAX(numberprice) FROM @cash WHERE idperson = @IdPer)
    and c.idperson = @IdPer
    )AS mainTable
GROUP BY maintable.pname,maintable.customername,maintable.NumId

Output