Databases
StackOverflow2013
Postsdbo
PODisplay XML column data from pivot as individual columns
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODisplay XML column data from pivot as individual columns
    text
    copied!<p>I have a table that is structured,</p> <blockquote> <p>Columns: month_yyyy, account_id, account_name, revenue</p> </blockquote> <p>I'd like to pivot the data in table so it's displayed as </p> <blockquote> <p>Columns: account_id, account_name, month_yyyy1 (revenue), month_yyyy2 (revenue)... monthyyyy_n (revenue)</p> </blockquote> <p>I did this,</p> <pre><code> select * from ( SELECT month_yyyy, account_id, account_name, revenue FROM table order by to_date(month_yyyy, 'month yyyy') ) pivot xml( sum (revenue) as revenue for (month_yyyy) in (select distinct month_yyyy from table) ) order by account_name; </code></pre> <p>This gives data like for the pivoted xml column,</p> <pre><code>&lt;PivotSet&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;April 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;August 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;December 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;Decmber 2011&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;February 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;February 2013&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;January 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;January 2013&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;July 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;June 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;March 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;March 2013&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;May 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;November 2011&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;November 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;October 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "MONTH_YYYY"&gt;September 2012&lt;/column&gt;&lt;column name = "REVENUE"&gt;0&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet&gt; </code></pre> <p>How do i create a view that selects and displays the xml column data as individual columns by month? I want to achieve, </p> <blockquote> <p>account_id, account_name, month_yyyy1 (revenue), month_yyyy2 (revenue)... monthyyyy_n (revenue)</p> </blockquote> <p>Thanks for any assistance given and please let me know if further info is required.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload