# How to Calculate value from comma separater [Answered]RSS

## 7 replies

Last post Feb 11, 2019 03:29 PM by salman behera

• salman beher...

All-Star

21479 Points

5971 Posts

### How to Calculate value from comma separater

Feb 05, 2019 11:37 AM|salman behera|LINK

Hi,

my three table like

 1 Basic 2 Day All 3 HRA 4 PF 5 ESI
 Employee_id PayHeadId Amount E001 1 2000 E001 2 50 E001 3 100

 PayHeadId MapTo FLAG 1 NULL LUM 2 NULL LUM 3 NULL LUM 4 1;2;3 FORMULA 5 1;2 FORMULA

I want a function which breaks Map to column value where the flag is Formula and fetches SUM of the amount column from the 2nd table.

example for payHeadId-4 value would be 2150

Sincerely,
Salman
• yrb.yogi

Star

10283 Points

2355 Posts

### Re: How to Calculate value from comma separater

Feb 06, 2019 06:23 AM|yrb.yogi|LINK

```----------------------------------CREATE THIS FUNCTION----------------------------------
CREATE FUNCTION [dbo].[SplitString]
(
@String varchar(8000), @Delimiter char(1)
)
returns @temptable TABLE (items varchar(8000),itemno int)
as
begin
declare @idx int
declare @i int=1
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null  return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
BEGIN
insert into @temptable(Items,itemno) values(@slice,@i)
SET @i+=1
END

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
----------------------------------CREATE THIS FUNCTION----------------------------------

----------------------------------USE BELOW SAMPLE----------------------------------
DECLARE @TABLE TABLE
(
Employee_id VARCHAR(10),PayHeadId INT,Amount INT
)

INSERT INTO @TABLE
(
)
SELECT 'E001',1,2000 UNION ALL
SELECT 'E001',2,50 UNION ALL
SELECT 'E001',3,100

DECLARE @MapTABLE TABLE
(
PayHeadId INT,MapTo VARCHAR(100),FLAG VARCHAR(100)
)
INSERT INTO @MapTABLE
(
)
SELECT 1,NULL,'LUM' UNION ALL
SELECT 2,NULL,'LUM' UNION ALL
SELECT 3,NULL,'LUM' UNION ALL
SELECT 4,'1;2;3','FORMULA' UNION ALL
SELECT 5,'1;2','FORMULA'

;WITH CTE AS
(
SELECT FLAG,items AS ItemWiseId,TT.PayHeadId
FROM @MapTABLE AS TT
CROSS Apply dbo.SplitString(TT.MapTo,';')
WHERE TT.MapTo IS NOT NULL
)
SELECT FLAG,CTE.PayHeadId,SUM(Amount) AS Total
FROM CTE
INNER JOIN @TABLE AS TT ON CTE.ItemWiseId=TT.PayHeadId

-------------------OUTPUT-----------
--FORMULA	4	2150
--FORMULA	5	2050```

• Ackerly Xu

Contributor

3460 Points

1300 Posts

### Re: How to Calculate value from comma separater

Feb 06, 2019 07:58 AM|Ackerly Xu|LINK

Hi salman behera,

You could try the sql below.

```select case when MapTo is null then ''
else

(select sum(Amount) from employee where PayHeadId in    -- sum the amount with the converted string   (select [text]    from (  select
b.text     --select the converted result 1;2;3 will be                    --1               --2               --3
from
(select [text]=convert(xml,'<root><v>'+replace(MapTo ,';','</v><v>')+'</v></root>') ) as a           -- replace 1;2;3 with <root><v>1</v><v>2</v><v>3</v></root>

outer apply    -- join the tow result set
(select [text]=C.v.value('.','nvarchar(100)') from a.[text].nodes('/root/v') C(v)) b) as c))

end  as col, MapTo,Flag  from Head   -- head is your third table```

The result.

```col     MapTo   FLAG0	NULL	LUM
0	NULL	LUM
0	NULL	LUM
2150	1;2;3	FORMULA
2050	1;2	FORMULA```

Best regards,

Ackerly Xu

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• salman beher...

All-Star

21479 Points

5971 Posts

### Re: How to Calculate value from comma separater

Feb 08, 2019 02:53 AM|salman behera|LINK

can you please convert the same to sql server 2008...

Regards,

Sincerely,
Salman
• limno

All-Star

123068 Points

9963 Posts

Moderator

### Re: How to Calculate value from comma separater

Feb 11, 2019 03:00 PM|limno|LINK

Instead of struggling with code, resign your third table to make it into normal form. Keep single value in each cell.

With a normarlized table you can use a sum in your select to get the result.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• salman beher...

All-Star

21479 Points

5971 Posts

### Re: How to Calculate value from comma separater

Feb 11, 2019 03:07 PM|salman behera|LINK

Hi,

can you please give me an example??

what is the best way to do the same.

Thanks,

Sincerely,
Salman
• limno

All-Star

123068 Points

9963 Posts

Moderator

### Re: How to Calculate value from comma separater

Feb 11, 2019 03:22 PM|limno|LINK

```PayHeadId	MapTo
1	NULL
2	NULL
3	NULL
4	1
5	2
6	3
7	1
8	2```

--Join this modified table  with your other table and sum it up.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• salman beher...

All-Star

21479 Points

5971 Posts

### Re: How to Calculate value from comma separater

Feb 11, 2019 03:29 PM|salman behera|LINK

Thanks All..