How to merge columns of two different tables into one table

Question

DECLARE @String VaRCHAR(100) = 'Column1|Column2|Column3' DECLARE @StringColumns VaRCHAR(100) = 'EmpID|EmpName|EmpDate' Select 1 as Items,value as Columns1 from dbo.Split('|',@String) Select 1 as Items,value as ColumnsTexts from dbo.Split('|',@StringColumns) These are the scripts for two sample tables which I would like to combine in a way given in screenshot.

Pravat Kumar · Answer

Hello Chintan, please execute below query once. CREATE FUNCTION fn_SplitString( @Input NVARCHAR(MAX) ,@Character CHAR(1) ) RETURNS @Output TABLE (Item NVARCHAR(1000)) AS 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

How to merge columns of two different tables into one table

Answers (1)