{"id":909,"date":"2015-07-23T19:54:54","date_gmt":"2015-07-23T19:54:54","guid":{"rendered":"http:\/\/www.van-maanen.com\/?p=909"},"modified":"2015-07-23T19:54:54","modified_gmt":"2015-07-23T19:54:54","slug":"transpose-a-record-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=909","title":{"rendered":"transpose a record in Oracle"},"content":{"rendered":"<p>Transpose a record in Oracle isn&#8217;t easy. I had a small table with several records and one value in a record. I wanted to transpose that table into one record with the values adjacent to each other. The question: how to accomplish this? Recently, Oracle introduced the pivot facility that allowed this procedure. The code to undertake is as follows:<\/p>\n<pre>\nselect * from\n    (select peri_omsch, \n        ROW_NUMBER ()  \n        over (PARTITION BY een order by peri_omsch) as rn \n        from (select distinct '1' as een, peri_omsch from salesall order by peri_omsch))\n       pivot\n        (\n          min(peri_omsch)\n          for rn in (1 as a1,2 as a2,3 as a3,4 as a4,5 as a5,6 as a6,7 as a7,8 as a8,9 as a9,10 as a10,11 as a11,12 as a12)\n        );\n<\/pre>\n<p>Let us start with this table:<br \/>\n<a href=\"http:\/\/www.van-maanen.com\/wp-content\/uploads\/2015\/07\/Untitled1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.van-maanen.com\/wp-content\/uploads\/2015\/07\/Untitled1.png\" alt=\"Untitled\" width=\"162\" height=\"99\" class=\"alignnone size-full wp-image-919\" \/><\/a><br \/>\nThe sql fetches the values from the original table (peri_omsch) and it adds an additional field that contains the row number. The code to add that additional field uses a ranking function where the whole table is seen as one set with a fixed value (&#8216;een&#8217;) as an identifier for the set. Upon this set a rownumber is added.<br \/>\nWe get <\/p>\n<p><a href=\"http:\/\/www.van-maanen.com\/wp-content\/uploads\/2015\/07\/Untitled3.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.van-maanen.com\/wp-content\/uploads\/2015\/07\/Untitled3.png\" alt=\"Untitled\" width=\"168\" height=\"94\" class=\"alignnone size-medium wp-image-924\" \/><\/a><\/p>\n<p>I use this rownumber to create the columns. Each value of the rownumber (1, 2, 3 etc ) is translated into a column. Hereby 1 is translated into column a1, 2 is translated into column a2 etc. I store the peri_omsch in this column. To enforce a unique value, I used the min function, which enforces one unique value to be stored.<br \/>\nThe final result looks like:<br \/>\n<a href=\"http:\/\/www.van-maanen.com\/wp-content\/uploads\/2015\/07\/Untitled.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.van-maanen.com\/wp-content\/uploads\/2015\/07\/Untitled-300x55.png\" alt=\"Untitled\" width=\"300\" height=\"55\" class=\"alignnone size-medium wp-image-917\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Transpose a record in Oracle isn&#8217;t easy. I had a small table with several records and one value in a record. I wanted to transpose that table into one record with the values adjacent to each other. The question: how to accomplish this? Recently, Oracle introduced the pivot facility that allowed this procedure. The code [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":910,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-909","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/909","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=909"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/909\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=909"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=909"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=909"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}