To All,
If you what to advance your SQL knowledge, there is an outstanding book in print that you must purchase. It is "SQL Cookbook" by Anthony Molinaro. BTW, I received some code from Anthony (see below). But, I need your help in figuring out how to run this code in DB2 version 8 and up for LUW.

create table test1 (text1 varchar(5));

insert into test1 values(',,,,,');
commit;

insert into test1 values(',,');
commit;

insert into test1 values(',');
commit;

insert into test1 values('12345');
commit;

insert into test1 values('1');
commit;

insert into test1 values('aaaa');
commit;

insert into test1 values('BBBBB');
commit;

insert into test1 values('A,B,C');
commit;

select *
from (
select text1,
rtrim(
max(case when rn = 1 then pos end) ||','||
max(case when rn = 2 then pos end) ||','||
max(case when rn = 3 then pos end) ||','||
max(case when rn = 4 then pos end) ||','||
max(case when rn = 5 then pos end) ||',',',') positions,
cnt occurrences
from (
select y.text1,y.pos,
row_number()over(partition by y.text1 order by y.pos) rn,
count(*)over(partition by y.text1) cnt
from (
select test1.text1, iter.pos
from test1,
(select rownum pos from all_objects where rownum <= 100) iter
where iter.pos <= length(test1.text1)
) y
where substr(y.text1,y.pos,1) = ','
) z
group by text1,cnt
union
select text1,
null,
0
from test1
where length(replace(text1,',')) = length(text1)
)
order by 3 desc
/

-----------------------------
OUTPUT
-----------------------------

TEXT1 POSITIONS OCCURRENCES
------------------------------ ------------------------------ -----------
,,,,, 1,2,3,4,5 5
,, 1,2 2
A,B,C 2,4 2
, 1 1
1 0
12345 0
BBBBB 0
aaaa 0

8 rows selected.



****** select rownum pos from all_objects where rownum <= 100) iter
I think this line could be rewritten as:
select colno pos from sysibm.syscolumns where colno <= 100) iter


Please take a few minutes to provide feedback on the quality of service you received. The Department of Education values your feedback as a customer. Commissioner John L. Winn is committed to continuously assessing and improving the level and quality of services provided to you by Department staff. Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]

DB2 & UDB email list listserv db2-l LazyDBA home page