Another sql headscratcher

[darkgen]

I have a table as follows:

ColumnA  ColumnB
Fruit    Apple;Pear;Banana

What I need is a mechnism to iterate through the delimited values in ColumnB and generate a new table as follows.

ColumnA  ColumnB
Fruit    Apple
Fruit    Pear
Fruit    Banana

SELECT        a.ColumnA,
split.a.value('.', 'VARCHAR(100)') As ColumnB
from
(select ColumnA,

cast('<M>' + replace(ColumnB, ';', '</M><M>') + '</M>' AS XML) As ColumnB

from Table1
) as a cross apply ColumnB.nodes ('/M') as split(a)

Kinda gets there but balks where ColumnB is empty or does not contain the delimiter ";" (or at least I thinks that's why it balks.).

Appreciate any pointers, TIA Paul!

 

[mid_gen]

Not familiar with your DB (MSSQL?). Presented with this problem in Oracle I would write a PL/SQL procedure that iterates through the source records, generating new records into a table as required based on the content of the source, and inserts into the target table (using bulk collect operations for performance).

It's the kind of operation that doesn't lend itself particularly well to a SQL solution...although you may find your RDBMS has built-in functions that can perform a task like this fairly efficiently.

 

[meandu229]

Just fired up MSSQL and what you have looks fine.
Made the table

Select * from #fruit which contains

COLUMNA		COLUMNB
cars			ford;bmw;merc
fruit			apple;pear;other;orange
people		meandu229
flyingthings		planes;helicopters;ufos
empty	
nulls			NULL
nulls			NULL

ran your script and got

cars		ford
cars		bmw
cars		merc
fruit		apple
fruit		pear
fruit		other
fruit		orange
people	meandu229
flyingthings	planes
flyingthings	helicopters
flyingthings	ufos
empty

 

[darkgen]

Hmm; thanks for confirming. Something in the column data perhaps then.... I'll have a further play.

 

[darkgen]

Worked out the issue -
In the data, ColumnB the string values can contain ampersand; for example;

Acid Drops;Rhubarb&Custard;Mints

XML is treating that ampersand as a special character and failing. Is there a means to get it to ignore it as a special character? I Need to retain the string "as is" and not replace characters.

As a workaround I could REPLACE the data, prior and then post, to get around the failure.