Calculate Sum of comma separated value from columns in SQL Server

Hi PRA,

Refer the below example.

For splitString function refer below article.

Split and convert Comma Separated (Delimited) String to Table in SQL Server

SQL

DECLARE @Fruits AS TABLE(Id INT,Name VARCHAR(10),Price INT)
INSERT INTO @Fruits VALUES(1,'Apple',5)
INSERT INTO @Fruits VALUES(2,'Orange',13)
INSERT INTO @Fruits VALUES(3,'Oil',25)
INSERT INTO @Fruits VALUES(4,'Cherry',12)

DECLARE @Person AS TABLE(Id INT,Person VARCHAR(10),IdFood VARCHAR(50),NumberKg VARCHAR(50))
INSERT INTO @Person VALUES(1,'Rustam','1,2,4','2,3,1')
INSERT INTO @Person VALUES(2,'Sadriddin','2,3,1,4','1,1,1,1')
INSERT INTO @Person VALUES(3,'Nurullo','4','3')

DECLARE @Result AS TABLE(Id INT,Person VARCHAR(10),Fruit INT,Number INT)

DECLARE @Counter INT
SET @Counter = 1
WHILE (@Counter <= (SELECT COUNT(*) FROM @Person))
BEGIN	
	DECLARE @Id INT,@Name VARCHAR(10),@Total INT,@Food VARCHAR(50),@Number VARCHAR(50)
	SET @Id = (SELECT Id FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Id FROM @Person) t WHERE t.SiNo = @Counter)
	SET @Name = (SELECT Person FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Person FROM @Person) t WHERE t.SiNo = @Counter)
	SET @Food = (SELECT IdFood FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,IdFood FROM @Person) t WHERE t.SiNo = @Counter)
	SET @Number = (SELECT NumberKg FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,NumberKg FROM @Person) t WHERE t.SiNo = @Counter)
	
	DECLARE @CounterInner INT
	SET @CounterInner = 1
	WHILE (@CounterInner <= (SELECT COUNT(Item) from dbo.SplitString(@Food,',')))
	BEGIN
		DECLARE @FoodId INT, @NumberKg INT
		SET @FoodId = (SELECT Item FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Item from dbo.SplitString(@Food,',')) t WHERE t.SiNo = @CounterInner)
		SET @NumberKg = (SELECT Item FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Item from dbo.SplitString(@Number,',')) t WHERE t.SiNo = @CounterInner)
		INSERT INTO @Result VALUES(@Id,@Name,@FoodId,@NumberKg)

		SET @CounterInner = @CounterInner  + 1
	END

    SET @Counter = @Counter  + 1
END

SELECT r.Id,r.Person,SUM(f.Price * r.Number) Summa FROM @Result r
INNER JOIN @Fruits f ON f.Id = r.Fruit
GROUP BY r.Id,r.Person

Output