SQL Server

hi

i was able to use user defined function to split the string into small pieces

from this we have to ...find before x one and identify and multiply value ..
seems complicated ..

simple cursor .. character by character LOOP of string .. with logic ... if then else ..
will do it
i think ...

please click arrow to the left for UDF and SQL ...
drop function [dbo].[fn_split_string_to_column]
go 

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END
GO 

;with cte as 
(
select rate ,value 
from Temp  cross apply [dbo].[fn_split_string_to_column](rate,' ')
)
select a.Rate,b.value from cte  a cross  apply [dbo].[fn_split_string_to_column](value,',')  b