How to eliminate Nulls, and insert with split delimter

Question

drop table #temp

Create Table #Temp 
( 
col1 Varchar(20), 
col2 Varchar(20), 
Col3 Varchar(50), 
col4 Varchar(20)
)

Select * From #Temp 

Insert Into #Temp(col1)
Select * From SplitDelimiterString('123,456', ',')

Insert Into #Temp(col2)
Select * From SplitDelimiterString('abc,def', ',')

Insert Into #Temp(Col3)
Select * From SplitDelimiterString('fff,ggg', ',')

Insert Into #Temp(col4)
Select * From SplitDelimiterString('520002,520003', ',')

Select * From #Temp

FYI, SplitDelimiterString is a function. -- Code for SplitDelimiterString

Create FUNCTION [dbo].[SplitDelimiterString] (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))

RETURNS @ItemTable TABLE (Item VARCHAR(8000))

AS
BEGIN
    DECLARE @StartingPosition INT;
    DECLARE @ItemInString VARCHAR(8000);

    SELECT @StartingPosition = 1;
    --Return if string is null or empty
    IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 

    WHILE @StartingPosition > 0
    BEGIN
        --Get starting index of delimiter .. If string
        --doesn't contain any delimiter than it will returl 0 
        SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 

        --Get item from string        
        IF @StartingPosition > 0                
            SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
        ELSE
            SET @ItemInString = @StringWithDelimiter;
        --If item isn't empty than add to return table    
        IF( LEN(@ItemInString) > 0)
            INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            

        --Remove inserted item from string
        SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                     LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)

        --Break loop if string is empty
        IF LEN(@StringWithDelimiter) = 0 BREAK;
    END

    RETURN
END

-- The result set is

Col1    Col2      Col3    Col4

123     NULL      NULL    NULL
456     NULL      NULL    NULL
NULL    abc       NULL    NULL
NULL    def       NULL    NULL
NULL    NULL      fff     NULL
NULL    NULL      ggg     NULL
NULL    NULL      NULL    520002
NULL    NULL      NULL    520003

-- I need a result set like
-- The result set is

col1      col2      col3      col4

123       abc       fff       520002
456       def       ggg       520003

Please help.

Answer 1

--- Figured out my self. Thanks to @liebs19 for logic

BEGIN TRAN

Create Table #Temp1

( 

RowID int not null identity(1,1) primary key,

col1 Varchar(20), 

)

Create Table #Temp2 

( 

RowID int not null identity(1,1) primary key,

col2 Varchar(20), 

)

Create Table #Temp3 

( 

RowID int not null identity(1,1) primary key,

col3 Varchar(20), 

)

Create Table #Temp4 

( 

RowID int not null identity(1,1) primary key,

col4 Varchar(20), 

)

Insert Into #Temp1(col1)

Select * From SplitDelimiterString('123,456', ',')


Insert Into #Temp2(col2)

Select * From SplitDelimiterString('abc,def', ',')

Insert Into #Temp3(Col3)

Select * From SplitDelimiterString('fff,ggg', ',')

Insert Into #Temp4(col4)

Select * From SplitDelimiterString('520002,520003', ',')


Select  #Temp1.Col1, #Temp2.col2, #Temp3.Col3, #Temp4.Col4 

From #Temp1 

Inner Join #Temp2 ON #Temp1.RowID = #Temp2.RowID

Inner Join #Temp3 ON #Temp1.RowID = #Temp3.RowID

Inner Join #Temp4 ON #Temp1.RowID = #Temp4.RowID 


ROLLBACK TRAN

-- This is the output finally I am looking for.

col1      col2      col3      col4

123       abc       fff       520002
456       def       ggg       520003

.