Hi! I tryed three days, but couldn't solve this. Its show duplicate in result. I don't know where my mistake.
DECLARE @MANUALSICK AS TABLE (Id INT, NAMEBOL VARCHAR(150), price INT)
INSERT INTO @MANUALSICK VALUES (9,'Тахлили умумии пешоб',5)
INSERT INTO @MANUALSICK VALUES (107,'Муайян намудани гурухи хун ва резус омил',14)
DECLARE @SPR_VRCH AS TABLE (KOD_VR INT, NAIM_VR VARCHAR(70))
INSERT INTO @SPR_VRCH VALUES(7,'Холмуродова Гулрухсор')
DECLARE @kassa AS TABLE (Id INT,iddoc int ,name_shah VARCHAR(30),rasid_roh VARCHAR(10), talon_roh INT)
INSERT INTO @kassa VALUES(1,7,'Мадралиева Ш','9,107',7)
DECLARE @IdPer INT
SET @IdPer = 7
SELECT PersonId,
STUFF((SELECT ',' + NAMEBOL
FROM(
SELECT k.talon_roh PersonId,LTRIM(RTRIM(m.NAMEBOL))NAMEBOL
FROM @MANUALSICK m, @kassa k
WHERE m.Id IN (SELECT CAST(item AS INTEGER) FROM
dbo.SplitString(k.rasid_roh, ','))
AND k.IdDoc = @IdPer
) as childTable
WHERE mainTable.PersonId = childTable.PersonId
FOR XML PATH('')), 1, 1, ' ') AS Name,
naim_vr,
name_shah,
(SELECT SUM(m.Price)
FROM (
SELECT CAST(Item AS INTEGER) Item
FROM dbo.SplitString((SELECT TOP 1 rasid_roh
FROM @kassa
WHERE IdDoc = @IdPer
ORDER BY talon_roh DESC), ',')
)t
INNER JOIN @MANUALSICK m ON m.Id = t.Item) Price
FROM(
SELECT k.talon_roh PersonId,v.NAIM_VR,k.name_shah,m.PRICE
FROM @kassa k
JOIN @SPR_VRCH v ON k.IdDoc = v.KOD_VR, @MANUALSICK m
WHERE k.talon_roh = (SELECT MAX(talon_roh) FROM @kassa WHERE IdDoc = @IdPer)
and k.IdDoc = @IdPer
)AS mainTable
GROUP BY mainTable.NAIM_VR,mainTable.name_shah,mainTable.PersonId,mainTable.PRICE