asked
Parsing out a column into multiple rows in new table.
Hello All - I'm new to the boards - and "semi" new to SQL, and am having difficulty in parsing out a column containing multiple values into separate rows. Here is a link for the DDL editor
http://sqlfiddle.com/#!3/00388/3 I have a file of 100 records - where the 2nd field contains either 2 or 3 phones numbers (delimited by comma). I want to create a separate table where each phone# is written as a new row (so essentially a phone table which will be linked back by sku field). SO in this example - the first 8 records will generate two rows each (since 2 phone# in each field), and the 9th record will generate 3 rows, etc.. So something like: SKU COLUMN 1 101 (978) 371-3802 101 (781)344-1202 102 (508)650-9900 102 (781)793-0330 103 (781) 613-6100 103 (617)946-4800 . . . 109 (508)359-4107 109 (800)528-1530 x120 109 (781)489-1030 Any help is appreciates - Thanks!
select t.sku, phone = stringval from showme t cross apply dbo.CSVTable(item) i am using a string parsing function CSVTable(), you can get it from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
Thanks for quick reply - but I am getting an error while trying to run "Incorrect syntax near 'int'.: CREATE function CSVTable(@Str varchar(7000)) returns @t table (numberval int, stringval varchar(100), DateVal datetime) as begin declare @i int"
http://sqlfiddle.com/#!3/824a4/1 Do I need to make some change to the function itself?
on your SQL Management Studio, (not sqlfiddle) 1. copy and paste the CSVTable script, starting from CREATE FUNCTION to a query window and execute it. This will create the CSVTable function in your database. 2. execute the query i posted. It will give you the result that you need
The solution is correct. However, there are better ways of creating a table from a CSV. A WHILE loop is not at all efficient. Re4ad this article:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thanks for this!
15 People are following this question.
