This article has moved to
plsqlnotes.blogspot.com
May 30, 2007
Using Model for generating CSV by Partition
2 Comments »
RSS feed for comments on this post. TrackBack URI
This article has moved to
plsqlnotes.blogspot.com
RSS feed for comments on this post. TrackBack URI
You should warn people that your solution only works when THEDATE is a NOT NULL column. Otherwise you can get results like the following with this approach.
select * from custtab order by thesortkey ;
CUST THEDATE THESORTKEY
———- ———- ———-
2 1 1
2 2 2
2 3
2 4 4
2 5 5
5 rows selected.
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 thesortkey) 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
———- ——————————
2 1,2
1 row selected.
In this case CSV should have been “1,2,,4,5″, not “1,2″.
Consider adding “WHERE thedate is not null” to your query just to be safe. Using PRESENTV also avoids the problem with nulls (in fact, that’s why I used it in the SQL Snippets solution you referenced), but if you really don’t like calling PRESENTV then there’s always this tweak.
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 thesortkey) as therow)
measures ( thedate, cast (null as varchar2(400)) as csv , 1 AS THEITERATIONFLAG)
rules update
iterate(9999) until THEITERATIONFLAG[iteration_number +2] is null
(csv[1] = csv[1]||’,'||thedate[iteration_number +1])
order by 1;
CUST CSV
———- ——————————
2 1,2,,4,5
1 row selected.
HTH.
–
Joe Fuda
http://www.sqlsnippets.com/
Comment by SnippetyJoe — June 8, 2007 @ 6:43 pm
All good points Joe. I agree that if your “ORDER BY” column is not the same as your CSV column, then the tweak you have shown would be necessary. Also, as you have illustrated, if you want to preserve NULL values in your CSV ( i.e. 4,,5,6 instead of 4,5,6 ) then my solution will not work.
Just for fun, here is another solution that
1. allows ORDER BY on a non CSV column
2. preserves null columns
3. provides FIXED formated output
`
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 ind) as therow)
measures ( nvl(cast(thedate as char(6)),’ ’) 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 , ,336
6 333 ,111 ,222
7 ,666 ,555 ,
`
Comment by Michael Moore — June 10, 2007 @ 3:04 am