{"id":68,"date":"2009-07-21T09:05:00","date_gmt":"2009-07-21T07:05:00","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/2009\/07\/unpivot-with-multi-column-groups-in-oracle-11g\/"},"modified":"2009-07-21T09:05:00","modified_gmt":"2009-07-21T07:05:00","slug":"unpivot-with-multi-column-groups-in-oracle-11g","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2009\/07\/unpivot-with-multi-column-groups-in-oracle-11g\/","title":{"rendered":"UNPIVOT with multi-column groups in Oracle 11g"},"content":{"rendered":"<p>Welcome to my first blog \ud83d\ude42 And I&#8217;ll start with something simple, but very cool at the same time.<\/p>\n<p>I really like Oracle 11g database and one of my favourite features is the PIVOT\/UNPIVOT clause in SQL. PIVOT is used to present rows as columns and UNPIVOT is the opposite operation, columns are presented as rows. I have found them very useful for <a href=\"http:\/\/otn.oracle.com\/apex\/\">APEX<\/a> applications, when I need to present data in table rows as columns in an APEX form for user input.<\/p>\n<p>PIVOT feature seems to have recieved a lot of bloggers attention, but I haven&#8217;t read much about UNPIVOT. Using UNPIVOT with one column is pretty straight forward and easy, just a small example (I use Oracle Database 11.1.0.7 Enterprise Edition for all the examples here):<\/p>\n<pre>\ncreate table unpivot_ex1 (\n id number primary key,\n jan number,\n feb number,\n mar number,\n apr number\n);\n<\/pre>\n<p>Fill it up with test data:<\/p>\n<pre>\ninsert into unpivot_ex1 (id, jan, feb, mar, apr)\n values (1, 1, 2, null, 4);\ninsert into unpivot_ex1 (id, jan, feb, mar, apr)\n values (2, 5, 6, null, 8);\ninsert into unpivot_ex1 (id, jan, feb, mar, apr)\n values (3, 9, 10, null, null);\ncommit;\n\nSELECT * FROM unpivot_ex1;\n\n    ID        JAN        FEB        MAR        APR\n---------- ---------- ---------- ---------- ----------\n     1          1          2                     4\n     2          5          6                     8\n     3          9         10\n<\/pre>\n<p>Now, if you want to select JAN, FEB, MAR, APR columns as separate rows, then its possible to use the simplest UNPIVOT clause:<\/p>\n<pre>\nSELECT * FROM unpivot_ex1 \n  UNPIVOT INCLUDE NULLS\n    (monthly_value FOR month IN (jan, feb, mar, apr));\n\n        ID MON MONTHLY_VALUE\n---------- --- -------------\n         1 JAN             1\n         1 FEB             2\n         1 MAR\n         1 APR             4\n         2 JAN             5\n         2 FEB             6\n         2 MAR\n         2 APR             8\n         3 JAN             9\n         3 FEB            10\n         3 MAR\n         3 APR\n\n12 rows selected.\n<\/pre>\n<p>Now, lets take a more interesting case. What if some of the columns are combined into logical groups and you want to preserve them in one row? For example:<\/p>\n<pre>\ncreate table unpivot_ex2 (\n  id number primary key,\n  jan_income number,\n  jan_expense number,\n  feb_income number,\n  feb_expense number,\n  mar_income number,\n  mar_expense number\n);\n<\/pre>\n<p>For every month there is income and expense column.<\/p>\n<pre>\ninsert into unpivot_ex2 values (1, 1, 1, 2, 3, 5, 2);\ninsert into unpivot_ex2 values (2, 5, 6, 4, 4, null, null);\ncommit;\n\nSELECT * FROM unpivot_ex2;\n\n        ID JAN_INCOME JAN_EXPENSE FEB_INCOME FEB_EXPENSE MAR_INCOME MAR_EXPENSE\n---------- ---------- ----------- ---------- ----------- ---------- -----------\n         1          1           1          2           3          5           2\n         2          5           6          4           4\n<\/pre>\n<p>UNPIVOT allows to create column groups, like this:<\/p>\n<pre>\nSELECT * FROM unpivot_ex2\n  UNPIVOT INCLUDE NULLS\n    ((income, expense) FOR month IN (\n     (jan_income, jan_expense), (feb_income, feb_expense), \n     (mar_income, mar_expense)));\n\n        ID MONTH                      INCOME    EXPENSE\n---------- ---------------------- ---------- ----------\n         1 JAN_INCOME_JAN_EXPENSE          1          1\n         1 FEB_INCOME_FEB_EXPENSE          2          3\n         1 MAR_INCOME_MAR_EXPENSE          5          2\n         2 JAN_INCOME_JAN_EXPENSE          5          6\n         2 FEB_INCOME_FEB_EXPENSE          4          4\n         2 MAR_INCOME_MAR_EXPENSE\n\n6 rows selected.\n<\/pre>\n<p>How cool is that? \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Welcome to my first blog \ud83d\ude42 And I&#8217;ll start with something simple, but very cool at the same time. I really like Oracle 11g database and one of my favourite features is the PIVOT\/UNPIVOT clause in SQL. PIVOT is used to present rows as columns and UNPIVOT is the opposite operation, columns are presented as [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,10],"class_list":["post-68","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":0,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}