asked on
-- declare variables
declare @tempstr varchar(2000)
declare @col varchar(2000)
declare @table nvarchar(2000)
-- Step 1 - Create temp table called tbl with one column called in and set its value to this string
CREATE TABLE #tbl (line VARCHAR(1000))
-- insert record into table
INSERT INTO #tbl
VALUES ('"Last Name","FirstName","Email Address","Order of Operations","One, Two and Multistep Equations"');
-- Step 2 - save into variables
SET @col = ''
SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), ',')) FROM #tbl)
-- Step 3 - iterate through the string to create table column names
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 + ' varchar(100)'
-- Step 4 - create a table called Algebra1Scores with the column names created in step 3
SET @table = 'CREATE TABLE Algebra1Scores (' + @col + ')'
-- view the value stored in the variable called @col
SELECT @table