tag:blogger.com,1999:blog-3366935554564939610.post8182634975616321155..comments2008-12-06T14:47:03.848-05:00Michael J. A. Berryhttp://www.blogger.com/profile/06077102677195066016noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-3366935554564939610.post-85343436036002464332008-12-06T13:56:00.000-05:002008-12-06T13:56:00.000-05:00One can do it in Oracle SQL this way:<BR/><BR/>create table productx <BR/>(id number, description clob)<BR/>/<BR/><BR/>create or replace type t_vc2_50 as table of varchar2(50)<BR/>/<BR/><BR/>select b.column_value token<BR/>, count(*)<BR/>from <BR/>(select &#39;,&#39;||description||&#39;,&#39; txt <BR/> from productx ) t<BR/>,table ( cast ( multiset <BR/> ( select substr (txt,<BR/> instr (txt, &#39;,&#39;, 1, level ) + 1,<BR/> instr (txt, &#39;,&#39;, 1, level+1) <BR/> - instr (txt, &#39;,&#39;, 1, level) -1 ) token<BR/> from dual<BR/> connect by level &lt;= length(t.txt)<BR/> -length(replace(t.txt,&#39;,&#39;,&#39;&#39;))-1<BR/> ) as t_vc2_50 ) ) b<BR/>group by b.column_value<BR/>/<BR/><BR/><BR/>The connect by level &lt;= .. is a way to generate a table with numbers from 1 to n. (the enumerate table)<BR/><BR/>One could also use user defined funtions. <BR/><BR/>RCAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-73008867788657516422008-07-05T17:01:00.000-04:002008-07-05T17:01:00.000-04:00Hi Gordon, <BR/><BR/>Great post! This is one of the most interesting observations I've seen on the Web in many months. <BR/><BR/>I think it can be done with SSIS/UDFs in 2008 and sparse tables.Anonymousnoreply@blogger.com