{"id":6777,"date":"2020-07-30T00:28:50","date_gmt":"2020-07-30T05:28:50","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=6777"},"modified":"2020-07-30T00:31:32","modified_gmt":"2020-07-30T05:31:32","slug":"oracle-listagg","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=6777","title":{"rendered":"Oracle &#8211; LISTAGG"},"content":{"rendered":"<p>I needed to collapse multiple rows into a single row &#8212; the circuits within a diversity set are stored within the ds_dvrsty_set_circuit table as individual rows &amp; the ds_dvrsty_set_id links the multiple rows. What I <em>wanted<\/em> was a set ID, set name, and the list of circuits within the set.<\/p>\n<p>To accomplish this, I found <a href=\"https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41084\/functions089.htm#SQLRF30030\" target=\"_blank\" rel=\"noopener noreferrer\">LISTAGG<\/a> which is a little bit like STUFF in MSSQL. This query produces a single row for each diversity set that contains the set ID, the set name, and a comma delimited list of set members.<\/p>\n<pre>SELECT\r\n     ds_dvrsty_set_circuit.ds_dvrsty_set_id,\r\n     (select ds_dvrsty_set.ds_dvrsty_set_nm from ds_dvrsty_set where ds_dvrsty_set_id = ds_dvrsty_set_circuit.ds_dvrsty_set_id) as set_name,\r\n     LISTAGG(ds_dvrsty_set_circuit.circuit_design_id,  ',') WITHIN GROUP(ORDER BY ds_dvrsty_set_circuit.ds_dvrsty_set_id) AS member_circuits\r\nFROM\r\n     ds_dvrsty_set_circuit\r\n     left outer join ds_dvrsty_set on ds_dvrsty_set.ds_dvrsty_set_id = ds_dvrsty_set_circuit.DS_DVRSTY_SET_ID\r\nWHERE\r\n     ds_dvrsty_set_circuit.ds_dvrsty_set_id in (select distinct ds_dvrsty_set_id from ds_dvrsty_set_circuit where circuit_design_id in (14445678, 5078901) )\r\nAND\r\n     ds_dvrsty_set.ds_dvrsty_set_nm like '43%'\r\nGROUP BY\r\n     ds_dvrsty_set_circuit.ds_dvrsty_set_id\r\nORDER BY\r\n     ds_dvrsty_set_circuit.ds_dvrsty_set_id;<\/pre>\n<p>Voila &#8212; exactly what I needed. If the searched circuit design IDs appear in more than one set, there is a new row for each set ID.<\/p>\n<p><a href=\"https:\/\/www.rushworth.us\/lisa\/?attachment_id=6779\" rel=\"attachment wp-att-6779\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-6779\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2020\/07\/oracle-listagg.png\" alt=\"\" width=\"536\" height=\"128\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2020\/07\/oracle-listagg.png 536w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2020\/07\/oracle-listagg-300x72.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2020\/07\/oracle-listagg-530x128.png 530w\" sizes=\"auto, (max-width: 536px) 100vw, 536px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I needed to collapse multiple rows into a single row &#8212; the circuits within a diversity set are stored within the ds_dvrsty_set_circuit table as individual rows &amp; the ds_dvrsty_set_id links the multiple rows. What I wanted was a set ID, set name, and the list of circuits within the set. To accomplish this, I found &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1009,1004],"tags":[1062,889,890,888,595,1061,1060],"class_list":["post-6777","post","type-post","status-publish","format-standard","hentry","category-metasolv-system","category-oracle","tag-listagg","tag-metasolv","tag-metasolv-solutions","tag-mss","tag-oracle","tag-oracle-function","tag-oracle-functions"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/6777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6777"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/6777\/revisions"}],"predecessor-version":[{"id":6780,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/6777\/revisions\/6780"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6777"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6777"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}