Split Comma Separated Values in Columns

Declare @Temp Table(ColumnA VarChar(40), ColumnB VarChar(400))

insert into @Temp Values('AA.ProjectBuildTower','2222, 3333, 4444, 5555')

insert into @Temp Values('BB.ProjectBuildFence','X900, 6789, 9000, 9876')

Declare @Output Table(Header VarChar(40), Data VarChar(20))

While Exists(Select * From @Temp Where CharIndex(',', ColumnB) > 0)   

      Begin       

            insert into @output(Header, Data)       

            Select ColumnA, Left(ColumnB, CharIndex(',', ColumnB)-1)       

            From   @Temp       

            Where  CharIndex(',', ColumnB) > 0       

           

            Update @Temp       

            Set    ColumnB = LTrim(Right(ColumnB, Len(ColumnB)-CharIndex(',', ColumnB)))   

      End

Insert Into @Output(Header, Data)

Select ColumnA, ColumnB From   @Temp

Select * From @Output Order By Header, Data

Select ColumnA, ColumnB From   @Temp