T-SQL Dynamic table create

So this is kind of a follow on from my last question

I have a string that looks like this:

Acc_id,Field label,Data point

I'd like to create a table from the above string using somthing like

CREATE TABLE #temp
    (Acc_id         NVARCHAR(MAX), 
     Field label    REAL,
     Data point     REAL) 

The commas seperate the columns - it needs to be dynamic so if more columns appear they get created in the table.

UPDATE: this is what I have thus far from the net but i need to make the first column NVARCHAR and the rest REAL rather than the other way round.

  declare @path NVARCHAR(MAX)
    SET @path = 'c:\temp\Book2.txt'

        declare @execSQL nvarchar(1000)
        declare @tempstr varchar(1000)
        declare @col varchar(1000)
        declare @table nvarchar(1000)

        -- Create a temp table to with one column to hold the first row of the csv file



          CREATE TABLE #tbl (line VARCHAR(1000))
           SET @execSQL = 
                'BULK INSERT #tbl  
                FROM ''' + @path + '''  
                WITH (  
                         FIELDTERMINATOR ='','',
                         FIRSTROW = 1,  
                         ROWTERMINATOR = ''\n'',
                         LASTROW = 1 
                      )         
               ' 

           EXEC sp_executesql @stmt=@execSQL 

        update #tbl set line = REPLACE(line,' ','_') where line like '% %'


           SET @col = ''
           SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), ',')) FROM #tbl)
           DROP TABLE #tbl
           WHILE CHARINDEX(',',@tempstr) > 0
            BEGIN           

               SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1))) + ' varchar(100),'     

               SET @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr)) 
            END
            SET @col = @col + @tempstr + ' real'

           IF Object_id('tempdb开发者_如何学Python..##temptable') IS NOT NULL 
           DROP TABLE #temptable 

           SET @table = 'create table ##temptable (' + @col + ')'

           EXEC sp_executesql @stmt=@table


        -- Load data from csv
           SET @execSQL = 
                'BULK INSERT ##temptable
                FROM ''' + @path + '''  
                WITH (  
                         FIELDTERMINATOR ='','',
                         FIRSTROW = 2,  
                         ROWTERMINATOR = ''\n''              
                      )         
               '  

           EXEC sp_executesql @stmt=@execSQL 

Thank you Rob

---

The Following works perfectly for what I want to do

declare @path NVARCHAR(MAX)

SET @path = 'c:\temp\Book2.txt'

    declare @execSQL nvarchar(1000)
    declare @tempstr varchar(1000)
    declare @col varchar(1000)
    declare @table nvarchar(1000)

    -- Create a temp table to with one column to hold the first row of the csv file

  IF Object_id('tempdb..#tbl') IS NOT NULL 
       DROP TABLE #tbl 

      CREATE TABLE #tbl (line VARCHAR(1000))
       SET @execSQL = 
            'BULK INSERT #tbl  
            FROM ''' + @path + '''  
            WITH (  
                     FIELDTERMINATOR ='','',
                     FIRSTROW = 1,  
                     ROWTERMINATOR = ''\n'',
                     LASTROW = 1 
                  )         
           ' 

       EXEC sp_executesql @stmt=@execSQL 

    update #tbl set line = REPLACE(line,' ','_') where line like '% %'


       SET @col = ''
       SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), ',')) FROM #tbl)

       DROP TABLE #tbl

    SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1))) + ' nvarchar(max),'     
    SET @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr))

       WHILE CHARINDEX(',',@tempstr) > 0
        BEGIN           

           SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1))) + ' nvarchar(max),'     

           SET @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr)) 
        END
        SET @col = @col + @tempstr + ' real'


       IF Object_id('tempdb..##temptable') IS NOT NULL 
       DROP TABLE ##temptable 

       SET @table = 'create table ##temptable (' + @col + ')'

       EXEC sp_executesql @stmt=@table


    -- Load data from csv
       SET @execSQL = 
            'BULK INSERT ##temptable
            FROM ''' + @path + '''  
            WITH (  
                     FIELDTERMINATOR ='','',
                     FIRSTROW = 2,  
                     ROWTERMINATOR = ''\n''              
                  )         
           '  

       EXEC sp_executesql @stmt=@execSQL 

select * from ##temptable

---

I would suggest doing a while loop and concatenating a string together for your table creation. This can beuseful for parsing through delimiter-separated lists. Something similar to the below should get you started.

set @IDList='Field1,Field2,Field3,'
set @i=1
set @pos =  patindex('%,%' , @IDList)
while @pos <> 0 begin
    -- Loop through Elements
    set @CurrentID= isnull(left(@IDList, @pos-1),null)

    set @SQLConstructor=@SQLConstructor+',sum('+@CurrentID+') as Column'+@si

    --- Reset loop
    set @IDList = stuff(@IDList, 1, @pos, '')
    set @pos =  patindex('%,%' , @IDList)
    set @i=@i+1
end