Divide a single column data to put into different rows.

[user1980]

Hello there

I have a strange issue and just wanted to know if it is possible in SQL 2005. Can somebody please guide me on this.
I have a column with data like
223 245 356
223 356 222
223
456
Null
223 456
etc (these are 3 digit numbers ie; first row contains 3 different values and last row has 2 different values. )

Is there a way that I can divide this data and insert into different rows.
Example, in one row I have to insert the first value of the first column, 223 and in the next row I have to insert 245 and in the next 356 and so on. So the data present in this column would go into 10 rows. Am I clear?

Please let me know if this can be achieved. Thank you for your time.

[ck9663]

Yes. You can parse that column and insert it into a table. Read the bottom part of this blog. You can use the same technique to parse your column and insert into a table. How big is your main table and what's the maximum number of value do you think your column has?

Happy Coding!!!

~~ CK

[user1980]

thank you for your response, I read the blog but I am not sure I understand it completely.
My main table is a growing table and it would go to thousands. But I would actually use a stored procedure that would insert my data(table-1) daily into another table(table-2). For this insert I need to split the values. So on an average I would deal from 10 to 30 records per day.
My table-1 has these values and the no:of values in the column may range from 0 - 6/7.

[ck9663]

You have to parse your column and insert each column to your table-2. If you look at the blog, it uses comma (,) as delimiter and break a single string into multiple rows. If you think of your column as the single string, you can convert that into a resultset and insert it into a table.

Happy Coding !!!

~~ CK

[nbiswas]

Try this(Xquery approach)

DECLARE @t TABLE(data VARCHAR(500))
INSERT INTO @t(data)
SELECT '223 245 356' UNION ALL
SELECT '223 356 222' UNION ALL
SELECT '223' UNION ALL
SELECT 456 UNION ALL
SELECT NULL UNION ALL
SELECT 223 456

DECLARE @tblDestinaiton(data VARCHAR(500))

Query:

INSERT INTO @tblDestinaiton(data)
SELECT 
 O.splitdata 
FROM
 (
 SELECT *,
 cast('<X>'+replace(F.data,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F
 )F1
 CROSS APPLY
 ( 
 SELECT fdata.D.value('.','varchar(500)') as splitdata 
 FROM f1.xmlfilter.nodes('X') as fdata(D)) O

Hope this helps