Merge 2 string based on Index each string separated by comma

1

Hi,

I have 2 variable string which are acted as array.

Example:-

select @colsUnpivot
= stuff((select ','+quotename(C.column_name)
from information_schema.columns as C
where C.table_name = 'TableName' and
C.column_name like '__%'
for xml path('')), 1, 1, '')

select @cols1='A1,B1,C1,D3,E5' -- The string is from @colsUnpivot
select @Cols2='coalesce(A1,-1),coalesce(B1,-1),coalesce(C1,-1),coalesce(D3,-1),coalesce(E5,-1)'

How can I get the output as below by merging both variable string based on index?

select Cols3='A1=coalesce(A1,-1),B1=coalesce(B1,-1),C1=coalesce(C1,-1),D3=coalesce(D3,-1),E5=coalesce(E5,-1)'

Please advise.

Thanks.

Regards,
Micheale

2

That's going to be very difficult without custom code since you have commas embedded in @cols2 and you are using commas to split the results.

That is:
coalesce(A1,-1),coalesce(B1, -1)
A comma separates the "A" value from the "B" value but it also appears in the coalesce. How does the code know which commas to split the data on? Should code ignore commas embedded in ()?

3

Which string?

4

Hi Sir,

I can get this 2 string into array:-

select * from [dbo].fn_split_string_to_column
select column_id,[value]=replace([value],'__',',') from [dbo].fn_split_string_to_column'),',')

ALTER 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

Now have to merge 2 based on column_id better solution?

I manage to did it using this method, but is there any better way to do it using Merge?

select @colsUnpivot = Replace(@colsunpivot,'[Brand],[ProductCode],[Description],[PriceType],[ReferenceSet],','')
select @colsUnpivot2 = Replace(Replace(Replace(@colsunpivot,'[Brand],[ProductCode],[Description],[PriceType],[ReferenceSet],',''),'[','coalesce('),']',',-1)')
SELECT @colsUnpivot3 = COALESCE(@colsUnpivot3 + ', ', '') + val
FROM (
select val =A.value+'='+B.value from
(
select * from [dbo].fn_split_string_to_column
)A join (
select column_id,[value]=replace([value],'__',',') from [dbo].fn_split_string_to_column'),',')
) B on A.column_Id = b.column_id
)Result

Please advise

Thanks

Regards,
Micheale