Comma delimited problems.

[bjswift]

I am working with a table with many rows, and multiple columns. One column has data seperated by a comma. Some rows have only one set of data, others may have 2, or 3 seperated by commas.

My problem is, I need to work with the way this data is setup, but must break out those comma delimited values, and place in seperate columns. I have some split functions that take a string and break it out depending on the delimeter, but it returns a table so I have no idea how to effectively break out the column into its respected rows.

Other than rewriting how the data is inserted into the table, how can I break out the record for that column, for each row in a mass select statement?

[srinika]

Hi,

I need some clarifications:

1. Is this to be done only once?
2. The data is in which database ?
3. With ur split function can u query the data and get a list of desired results set ?
4. If u have some sample data and the results that u expect as an example, it would be helpful

I have a few suggestions

- U Can use MS Excel to break in to columns?
- U can try something like follows im MS SQL Server:
Select col1, MySplitFn(col2), Col3, Col4 into NewTable from OldTable
- U can write a stored procedure and find the maximum columns needed in the new table and create that table and then insert data in to it

Srinika

[klintan]

Adding ab option to Srinikas list:

If you have SQL Server you can write a DTS task that splits the data.

[bjswift]

Answers to your questions:

1. Is this to be done only once?
No, it will be done to the data in the table every time the user wants to view it.

2. The data is in which database ?
Yes, the data is in the database, SQL server 2000, in a table with lets say 5 columns. The 5th column has data such as this '01_15.99,02_20.99,03_25.99'

3. With ur split function can u query the data and get a list of desired results set ?
I can call the split function like this: select * from split('one,two,three',',') and get a list returned of:
one
two
three

4. If u have some sample data and the results that u expect as an example, it would be helpful

Here is the example of one of the rows. I will | where there is a column change.

BEFORE:
Column1 | Column 2 | Column 3 | Column 4 | 01_15.99,02_20.99,03_25.99

AFTER:
Column1 | Column 2 | Column 3 | Column 4 | 01_15.99 | 02_20.99 | 03_25.99


As you can see, there are now 7 columns after the fact.

I hope this helps. For now, I plan on using the replace function and replace 01_ with a string, and just show one column to the extent of this:
USA: 15.99, CAN: 20.99, JAP: 25.99

I can get the desired results and keep everything in the same column if I use the replace, but the way it should be built, is to break it all out into seperate columns, to display to the user. I am trying to build a stored procedure to handle all of this, and I guess it might be possible to build a cursor to populate a temp table. Well, thanks for any input you may provide!

Brandon.

[exterminator]

BEFORE:
Column1 | Column 2 | Column 3 | Column 4 | 01_15.99,02_20.99,03_25.99
AFTER:
Column1 | Column 2 | Column 3 | Column 4 | 01_15.99 | 02_20.99 | 03_25.99The problem is not that tough as you are thinking it might be, IMO. I guess you probably are writing a view or something but that doesn't really matter. What you can do is write some string manipulation functions (or use the ones that you might be having already, i am not sure what you have) to get the splitted strings. This function should possibly be taking in as parameters - the actual string i.e. the column value having the comma seperated string and a number signifying the part of that string to extract like - if say I have 01_15.99,02_20.99,03_25.99 - as an argument to that UDF I would be passing this string and 1 if I want 01_15.99, 2 if I want 02_20.99 and 3 if I want 03_25.99. See if there is a function that can do this job for you else go ahead write down a UDF. And then if say that function is name "strtok" then the query would be something like:
SELECT Column1, Column2, Column3, Column4, strtok(Column5,1), strtok(Column5,2), strtok(Column5,3) FROM MyTable WHERE <the_where_condition>
That's all there is to it, IMO. If you need to throw this resultset into another table, use the SELECT INTO clause. Hope this helps. Regards.

[srinika]

This code I disn't test, thus u may need to play with it.

DECLARE @s VARCHAR(5000)
SET @s=''
SELECT @s=@s + Split(Col5) + ','

SELECT @s= 'Select ' + 'Col1, Col2, Col3, Col4,' + @s
' from MyTbl'


-- u can chk using --> Select @s and exec (@s) :)

Execute ('Insert into NewTbl Select ''' + @s + '''')

[bjswift]

Another problem I have, is that in column 5 (the column that has the data mashed together and seperated with commas) might not have the same amount of data in each row. Some might only have 01_15.99 some might have 01_...,02_...,03_..., ------ 25_...,26_... ect...

I have another table used as a lookup, that contains the max number the row might have, for example, right now we have 3 zones, so the max will be 03. I will check for that 3, and dynamically create a temp table using a cursor, then insert row for row I guess, into the temp table. That should give me a temp table with x amount of rows if it needs to grow, and populate nulls if the data isn't there.

Thanks for your input. It just seems that since the data is scrambled like it is, I will just have to write a bunch of code to work with it, even if it means using cursors - YUCK!

Brandon.

[srinika]

In my answer, the point that u raise is taken into consideration.

ie. it is written for variable number of comma seperated data, hence variable number of columns.

Even though the following way is inefficient, it would also be useful:

If u know the maximum number of comma seperated columns, (if u don't know u can easily find it) U can create the table with 4 + that much of columns, since u have 4 other Columns.
Then
- Get all records to a cursor,
- Use my earlier suggestion to create the Select Query
- Create the Query with Null for the remaining columns using a loop
ie. IF the max no of comma sep data is 5 and if the first record contains only 2 data elements, then
[data1,data2] ==> [data1],[data2],Null,Null,Null
- Use my earlier method to insert data to the table