By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Divide a single column data to put into different rows.

100+
P: 112
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.
Jan 21 '10 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Jan 21 '10 #2

100+
P: 112
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.
Jan 21 '10 #3

ck9663
Expert 2.5K+
P: 2,878
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
Jan 21 '10 #4

nbiswas
100+
P: 149
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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO @tblDestinaiton(data)
  2. SELECT 
  3.  O.splitdata 
  4. FROM
  5.  (
  6.  SELECT *,
  7.  cast('<X>'+replace(F.data,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F
  8.  )F1
  9.  CROSS APPLY
  10.  ( 
  11.  SELECT fdata.D.value('.','varchar(500)') as splitdata 
  12.  FROM f1.xmlfilter.nodes('X') as fdata(D)) O
Hope this helps
Mar 24 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.