Tuesday, February 14, 2006

STRAGG without PL/SQL - MODEL strikes again

Still playing around with MODEL. Last time I posted on using MODEL to break down a single columne containing comma separated values into multiple rows. This time, I'll go the other way, and take multiple rows and 'stragg' them up into a single comma separated list.

I use dense_rank to count, and sequence, the individual values. The order by clause in the function allows you to define how the 'fields' in the concatenated function will be ordered.

I'm using the 'return updated rows' clause to return fewer rows (ie just the one containing the concatenated value). I iterate up to 4000 times (maximum length of VARCHAR2) but quit when there isn't a row for that iteration (eg if there are five rows to be concatenated, there isn't a value for the sixth, so the iteration quits).

I'm not convinced this is the most efficient method as I think I'm iterating for each row selected. However at the moment, my involvement with 10G is purely 'play' (and all on XE), so I'm trying to experiment to see what is possible with MODEL. Once I've got more of its techniques under my belt, I'll have a better idea of how to improve my SQLs.

select data_type, seq, all_cols
from
(select data_type, cast(column_name as varchar2(4000)) cname,
dense_rank() over (partition by data_type order by column_name) -1 seq
from user_tab_columns
where table_name = 'PLAN_TABLE')
model
return updated rows
partition by (data_type)
dimension by (seq as seq)
measures (cname all_cols)
unique single reference
RULES ITERATE (4000)
UNTIL (PRESENTV(all_cols[ITERATION_NUMBER+1],1,0) = 0)
(all_cols[0] = case when ITERATION_NUMBER = 0
then all_cols[0]
else all_cols[0]||','||all_cols[ITERATION_NUMBER] end
)
order by 1,3,2;


PS. I'm trying performancing to publish this entry.

1 comment:

SnippetyJoe said...

Hi Igor,

You might be interested in my approach to this problem. I describe it here.
http://www.sqlsnippets.com/en/topic-11754.html