Using Model for generating CSV by Partition
This example assumes that "thedate"can not be NULL.
CUSTTAB
CUST THEDATE
---------- ----------
5 234
5 333
5 336
6 111
6 222
6 333
5 100
col csv format a30
SELECT cust,SUBSTR(csv,2) csv FROM custtab
MODEL
RETURN updated ROWS
PARTITION BY (cust)
DIMENSION BY (row_number() over (PARTITION BY cust ORDER BY thedate) AS therow)
MEASURES ( thedate,cast (NULL AS VARCHAR2(400)) AS csv)
RULES UPDATE
ITERATE(9999) UNTIL thedate[ITERATION_NUMBER +2] IS NULL
(csv[1] = csv[1]||','||thedate[ITERATION_NUMBER +1])
ORDER BY 1;
CUST CSV
---------- ------------------------------
5 100,234,333,336
6 111,222,333
2 rows selected.
Other model to CSV solution can be found at
http://forums.oracle.com/forums/message.jspa?messageID=1815489#1815489
and
http://www.sqlsnippets.com/en/topic-12092.html
In contrast to the example at the above URL, note the use of
UPDATE instead of UPSERT and the lack of a need for the "presentv" function.
The reference to a non-existent cell in "until thedate[ITERATION_NUMBER +2] is null"
returns NULL;
Intuition tells me that UPDATE should run faster than UPSERT,
but I've not actually tested it.
No comments:
Post a Comment