SQL Server: Add number columns dynamically based on a column values

I have a table as shown below. I needed to parse or extract the words into columns. For this I needed to first add columns dynamically.

To Create this table click below link

Show the script/code

Create Table AddColumnsDynamiccally ( SlNo int identity(1,1), MainColumn Varchar(100) ) insert into AddColumnsDynamiccally (MainColumn) select 'aa,b,c' UNION select 'aaa' UNION select 'aaa,bbb,ccc' UNION select 'aaa,bbb,ccc,dd,eee,fff,gg' UNION select 'aaa,bbb,ccc ddd' UNION select 'aaa,bbb,ccc,xx' UNION select 'aaa,mm' UNION select 'bbb,ccc' UNION select 'dd,aaaaa' UNION select 'mm,n,xx,yy#zzzz' UNION select 'p,q,r' select * from AddColumnsDynamiccally
close

For this I created below stored procedure can be used just to add total number of columns dynamically.

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------------------------------
-- No Of Parameters       :    3
-- Parameter Details      :   (1) Table Name
--                            (2) ColumnName to check number of words
--                            (3) Delimter to count number of words
-- Usage of SP            :    [AddColumnsDynamically] 'AddColumnsDynamiccally','MainColumn',','
-- Description            :    This SP will
--                            (1) Add one column by name NoOfWords and update number of words available in the specified column which is
--                                delimited with specified delimiter.
--                            (2) Adds columns "max(NoOfWords)" number columns.
---------------------------------------------------------------------------------------------
CREATE PROC [dbo].[AddColumnsDynamically] (@TableName varchar(100),@ColumnName varchar(100),@Delimiter Varchar(1))
As
BEGIN
    Declare @sql varchar(1000)

    -- Drop Column "NoOfWords" If Exists
    set @sql =    'IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'''+'NoOfWords'' AND Object_ID = Object_ID(N'''+'dbo.'+@TableName+'''))
                BEGIN
                    ALTER TABLE '+@TableName+' DROP COLUMN NoOfWords
                END'
    Exec(@sql)

    -- Add column "NoOfWords"
    set @sql = 'ALTER TABLE '+@TableName+' ADD NoOfWords int'
    Exec(@sql)

    -- Update Number of words available based on specified delimiter
    set @sql = 'Update '+ @TableName +' SET NoOfWords = len('+@ColumnName+') - len(replace('+@ColumnName+','''+@Delimiter+''',''''))+1'
    --print  @sql
    EXEC(@sql)

    Declare @a int
    Declare @n int
    Set @a = 1
    CREATE TABLE #tmp (cnt INT)
    set @sql = 'insert into #tmp Select max(NoOfWords) from '+ @TableName
    Exec(@sql)
    set @n = (select cnt from #tmp)
    --print @n

    -- adds columns here with columns prefixed with "Subcol" (we can change the name as per our requirement)
    -- NOTE/CAUTION: The below code drops the column and adds afresh.

    While @a<=@n

    BEGIN
    set @sql =    'IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'''+'SubCol'+cast(@a As varchar(10))+''' AND Object_ID = Object_ID(N'''+'dbo.'+@TableName+'''))
                BEGIN
                    ALTER TABLE '+@TableName+' DROP COLUMN SubCol'+cast(@a As varchar(10))+'
                END'
    Exec(@sql)
    --print @sql

    EXEC('ALTER TABLE '+@TableName+' ADD SubCol'+@a+' varchar(100)')
    set @a = @a + 1
    END

    select * from AddColumnsDynamiccally

END

Below is the resultant table generated by executing above SP.