Split one comma delimited string in a column into 100
Hello,
I have a table called tmp_tbl that only has one column called field1.
I populate that column with a single comma delimited string that has approx 100 commas or splits
How can I get this one column to create 100 columns with each piece of delimited data?
I have looked at every possible split function and can't find one that I can get to work.
Thanks in advance!
I have a table called tmp_tbl that only has one column called field1.
I populate that column with a single comma delimited string that has approx 100 commas or splits
How can I get this one column to create 100 columns with each piece of delimited data?
I have looked at every possible split function and can't find one that I can get to work.
Thanks in advance!
You can achieve this by Dynamic Queries.
Lets assume your table is "mytable" and the one column is "commacolumns"
Lets assume your table is "mytable" and the one column is "commacolumns"
Declare @buildtable VARCHAR(255)
SELECT @buildtable = 'SELECT ' + REPLACE(commacolumns, ',' , 'VARCHAR(30),') from mytable
SELECT @buildtable = @buildtable + ' INTO newtablename'
EXEC @buildtableASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
kdo,
Thanks for the reply....
There will be only several rows at any given time in the table.
Yes, this will be ran on a daily basis.
Thanks for the reply....
There will be only several rows at any given time in the table.
Yes, this will be ran on a daily basis.
ASKER
Deepak Subburajan,
I ran your solution and got an error of
"the name 'SELECT .......and then my string of data .... is not a valid identifier"
any ideas?
I ran your solution and got an error of
"the name 'SELECT .......and then my string of data .... is not a valid identifier"
any ideas?
ASKER
Vikas Garg,
Can you give an example of the use of the function?
Can you give an example of the use of the function?
H-SC,
Please read through your question once again. You mentioned "create 100 columns" which means you wanted to split the single column into multiple columns(same/new table). I think you were about to mean "split the single cell into multiple rows". Isn't it?
Please read through your question once again. You mentioned "create 100 columns" which means you wanted to split the single column into multiple columns(same/new table). I think you were about to mean "split the single cell into multiple rows". Isn't it?
Daily basis. Rats. So much for just copying the data into Excel and letting its tools split the data
Meta-SQL is probably the way to go. There is already a post steering you that way.
If all of the data is numeric,
SELECT 'INSERT INTO mytable SELECT ' + columnvalue FROM sometable;
Will generate the SQL to insert the data. However, it's more complicated if the columns are character types, or if every row doesn't contain the exact number of columns that are defined in the table.
Meta-SQL is probably the way to go. There is already a post steering you that way.
If all of the data is numeric,
SELECT 'INSERT INTO mytable SELECT ' + columnvalue FROM sometable;
Will generate the SQL to insert the data. However, it's more complicated if the columns are character types, or if every row doesn't contain the exact number of columns that are defined in the table.
ASKER
Deepak Subburajan,
Well I need to do the following
field1 in my table looks like this:
10022, test, test122, helloworld, , , test5, helloworld2,
I need field1 to break apart into multiple columns not as rows...
so field1 would now look like:
col1 col2 col3 col4 col5
10022 test test122 helloworld .......... and so on.
Well I need to do the following
field1 in my table looks like this:
10022, test, test122, helloworld, , , test5, helloworld2,
I need field1 to break apart into multiple columns not as rows...
so field1 would now look like:
col1 col2 col3 col4 col5
10022 test test122 helloworld .......... and so on.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How many rows are in the table?
Is this a process that you'll want to run more than once?