tag:blogger.com,1999:blog-35092861.post4628352245442723159..comments2023-05-11T06:36:14.487-06:00Comments on DB2 Diary from Radhesh: SQL Tips & Techniques- String AggregationRadhesh Kumarhttp://www.blogger.com/profile/00945889642759528415noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-35092861.post-25267651733798920552009-06-18T08:47:08.768-06:002009-06-18T08:47:08.768-06:00Hello and thanks for your script.
Just one thing ...Hello and thanks for your script.<br /><br />Just one thing :<br />I think the script need something more to be sure about the lenght of the VARCHAR list, especially on a long list :<br /><br />WITH<br /> t1(rowNum, category, name) AS<br /> ( SELECT rownumber() over(partition by category),<br /> category, name<br /> FROM myTab1 ),<br /> t2(category, list, cnt) AS<br /> ( SELECT category, <b>VARCHAR(name, <i>1500</i>)</b>, 1<br /> FROM t1<br /> WHERE rowNum = 1<br /> UNION ALL<br /> SELECT t2.category, <b>VARCHAR(t2.list || ', ' || t1.name, <i>1500</i>)</b>,<br /> t2.cnt + 1<br /> FROM t2, t1<br /> WHERE t2.category = t1.category AND<br /> t2.cnt + 1 = t1.rowNum )<br /><br />SELECT category, list<br />FROM t2<br />WHERE ( category, cnt ) IN (<br /> SELECT category,<br /> MAX(rowNum)<br /> FROM t1<br /> GROUP BY category )<br />ORDER BY 1<br /><br />MatthieuAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-35092861.post-74082467293684622482008-11-23T10:59:00.000-07:002008-11-23T10:59:00.000-07:00HelloMost of the posts are really amazing..!Hello<BR/><BR/>Most of the posts are really amazing..!Eldho Mathewhttps://www.blogger.com/profile/09939938898929931900noreply@blogger.comtag:blogger.com,1999:blog-35092861.post-56856046466529463312008-02-29T01:01:00.000-07:002008-02-29T01:01:00.000-07:00Frederik,Thanks for your comment and pointing to X...Frederik,<BR/>Thanks for your comment and pointing to XMLAGG function. More heads produce better solution in such scenario.<BR/><BR/>I had not looked at Serge's "SQL on Fire Part 1" until now. Even though we do not have a built-in function just for string aggregation, the XMLAGG function is pretty close to that. Not being familiar with XML functions, I overlooked the possibility of exploring XML functions outside XML world.<BR/><BR/>In my quick test, the query using XMLAGG showed the lowest estimated cost. However, the difference in performance would not be noticeable if you are querying a small table.<BR/><BR/>Thanks,<BR/>RadheshRadhesh Kumarhttps://www.blogger.com/profile/00945889642759528415noreply@blogger.comtag:blogger.com,1999:blog-35092861.post-49405205314489858242008-02-29T00:29:00.000-07:002008-02-29T00:29:00.000-07:00This comment has been removed by the author.Radhesh Kumarhttps://www.blogger.com/profile/00945889642759528415noreply@blogger.comtag:blogger.com,1999:blog-35092861.post-52587471339804890462008-02-28T02:22:00.000-07:002008-02-28T02:22:00.000-07:00Radhesh,This is a great way transforming a techniq...Radhesh,<BR/><BR/>This is a great way transforming a technique into something it was not exactly meant to be used for.<BR/><BR/>Did you know Serge Rielau did string concatenation using XMLAGG in his "SQL on Fire Part 1"? I am eager to see the performance results for both...<BR/><BR/>FrederikAnonymousnoreply@blogger.com