How to merge columns of two different tables into one table

Hello Chintan,

please execute below query once.

CREATE FUNCTION fn_SplitString(

@Input NVARCHAR(MAX)

,@Character CHAR(1)

)

RETURNS @Output TABLE (Item NVARCHAR(1000))

BEGIN

DECLARE @StartIndex INT

,@EndIndex INT

SET @StartIndex = 1

IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

BEGIN

SET @Input = @Input + @Character

END

WHILE CHARINDEX(@Character, @Input) > 0

BEGIN

SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output (Item)

SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

END

RETURN

END

DECLARE @String VARCHAR(100) = 'Column1|Column2|Column3'

DECLARE @StringColumns VARCHAR(100) = 'EmpID|EmpName|EmpDate'

SELECT ROW_NUMBER() OVER (

ORDER BY Item

) AS ID

,1 AS Items

,Item AS 'Columns' INTO #tempTable1

FROM dbo.fn_SplitString(@String, '|')

Select ROW_NUMBER() OVER (

ORDER BY Item

) AS ID,Item as ColumnsTexts INTO #tempTable2

from dbo.fn_SplitString(@StringColumns,'|')

select t1.Items,t1.Columns,t2.ColumnsTexts from #tempTable1 t1

JOIN #tempTable2 t2 ON t1.ID=t2.ID

1 Reply

Know the answer? Post it — somebody with the same question will find it here.