{"id":254,"date":"2010-04-30T09:37:59","date_gmt":"2010-04-30T09:37:59","guid":{"rendered":"http:\/\/www.thestupidbox.com\/blog\/?p=254"},"modified":"2021-06-25T04:16:57","modified_gmt":"2021-06-25T04:16:57","slug":"oracle-string-aggregation","status":"publish","type":"post","link":"http:\/\/thestupidbox.com\/blog\/oracle-string-aggregation\/","title":{"rendered":"Oracle String aggregation"},"content":{"rendered":"

This post is about string aggregation in oracle. Let’s say you have a table structure like below<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Country<\/th>\nState<\/th>\n<\/tr>\n<\/thead>\n
USA<\/td>\nCA<\/td>\n<\/tr>\n
USA<\/td>\nWA<\/td>\n<\/tr>\n
USA<\/td>\nTX<\/td>\n<\/tr>\n
India<\/td>\nTamil Nadu<\/td>\n<\/tr>\n
India<\/td>\nKerala<\/td>\n<\/tr>\n
India<\/td>\nAndhra Pradesh<\/td>\n<\/tr>\n
India<\/td>\nKarnataka<\/td>\n<\/tr>\n
UK<\/td>\nEssex<\/td>\n<\/tr>\n
UK<\/td>\nSurrey<\/td>\n<\/tr>\n
UK<\/td>\nKent<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

And if you want to get an output like this<\/p>\n\n\n\n\n\n\n\n
Country<\/th>\nStates<\/th>\n<\/tr>\n<\/thead>\n
USA<\/td>\nCA,WA,TX<\/td>\n<\/tr>\n
India<\/td>\nTamil Nadu, Kerala, Andhra Pradesh, Karnataka<\/td>\n<\/tr>\n
UK<\/td>\nEssex, Surrey, Kent<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

There are lot of ways you can achieve this. Like,
\ncollect<\/strong><\/p>\n

select country, CAST(COLLECT(states) AS number_ntt) AS States from table;<\/pre>\n
 (supported from 10g)<\/p>\n
in the above query, number_ntt is a table with below definition
\nCREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);<\/p>\n
And you have to write your own function to convert the table structure to a string. like this<\/a><\/p>\n
stragg<\/strong><\/p>\n
select country, stragg(states) from table;<\/pre>\n
this query is supported from only<\/p>\n
listagg<\/strong><\/p>\n
SELECT country, LISTAGG(states, ',') WITHIN GROUP (ORDER BY states) AS states\r\nFROM   table\r\nGROUP BY country;<\/pre>\n
this function also supported from 11g version only. But you have additional facility like, you can sort them.<\/p>\n
And there are few more techniques you can find them here<\/a><\/p>\n
XML<\/strong><\/p>\n
I read about one more nice method.. couldn’t remember the link :() which I think is the coolest of all… \ud83d\ude09<\/p>\n
select xmlelement(\"country\", xmlattributes(country as \"country\"), xmlagg(xmlelement(\"state\",state))) from table\r\ngroup BY country;<\/pre>\n
Once you get your data in XML format, you can format it to your desired output. XSL is for that… \ud83d\ude42 you can either format in oracle itself using XMLTRANSFORM function or using any XSL provider.<\/p>\n
after writing above all, found a very good article<\/a> of the same… I can’t blog anything if I have to write on my own. \ud83d\ude1b<\/p>\n
Happy querying!!<\/p>\n","protected":false},"excerpt":{"rendered":"
This post is about string aggregation in oracle. Let’s say you have a table structure like below Country State USA CA USA WA USA TX India Tamil Nadu India Kerala India Andhra Pradesh India Karnataka UK Essex UK Surrey UK… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[58],"tags":[66,64,69,68,65,67],"class_list":["post-254","post","type-post","status-publish","format-standard","hentry","category-technical","tag-aggregate-function","tag-oracle","tag-oracle-sorted-aggregate","tag-oracle-sql","tag-string-aggregate","tag-xml-aggregate"],"_links":{"self":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts\/254","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/comments?post=254"}],"version-history":[{"count":13,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions"}],"predecessor-version":[{"id":750,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions\/750"}],"wp:attachment":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/media?parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/categories?post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/tags?post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}