OraFAQ Forum: JDeveloper, Java & XML » Flatten output from "pivot xml"

Home » Developer & Programmer » JDeveloper, Java & XML » Flatten output from "pivot xml" (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Flatten output from "pivot xml" [message #489621]

Tue, 18 January 2011 03:00

spacebar
Messages: 26
Registered: February 2007

Junior Member

How do you query the output from the 'pivot xml' sql?

Just as an example, How can you query the output(xmltype) from this sql so that it is flattened into a relational type view?

select *
  from ( select owner, count(*) as owner_segment_count
          from  dba_segments
         where  owner in( 'OLAPSYS','ORDSYS','QDATA','SYS','SYSMAN','SYSTEM','XDB' )
         group by owner )
  pivot xml( sum( owner_segment_count ) as owner_segment_count for owner in( any ) )

CM: removed unescessary formatting. Personally I find size 1 hard on the eyes. Also added [code] tags, please do so yourself next time, see the orafaq forum guide if you're not sure how.

[Updated on: Tue, 18 January 2011 03:30] by Moderator

Report message to a moderator

Re: Flatten output from "pivot xml" [message #489628 is a reply to message #489621]

Tue, 18 January 2011 03:33

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post something that we can reproduce, it is not clear what you have and want.

Regards
Michel

Report message to a moderator

Re: Flatten output from "pivot xml" [message #489753 is a reply to message #489628]

Tue, 18 January 2011 14:45

spacebar
Messages: 26
Registered: February 2007

Junior Member

OK, Let me try to explain; This could be for any table, but let's say for example I want to get a count of segments by owner and I want the totals to be in column format(across) and not in row format(down), like this:

select *
 from  ( select owner, count(*) as owner_segment_count
          from  dba_segments
         group by owner )
pivot  ( sum( owner_segment_count )
         for owner in( 'SYSTEM', 'XDB', 'OLAPSYS', 'SYS', 'SYSMAN', 'QDATA', 'ORDSYS' ) )

SYSTEM XDB OLAPSYS  SYS SYSMAN QDATA ORDSYS
   742 799     249 2371   1685    11    142

The above sql works fine for any table if you know the values that should go in the 'pivot in clause'.

If you do not know the values that should go in 'pivot in clause' you need to use the 'XML' keyword with the 'pivot' clause, Like this:

select *
 from ( select owner, count(*) as owner_segment_count
         from  dba_segments
        group by owner )
pivot xml( sum( owner_segment_count ) as owner_segment_count for owner in( any ) )

Excerpt from SQL Language Reference:
XML: The optional XML keyword generates XML output for the query. The XML keyword permits the pivot_in_clause to contain either a subquery or the wildcard keyword ANY. Subqueries and ANY wildcards are useful when the pivot_in_clause values are not known in advance. With XML output, the values of the pivot column are evaluated at execution time. You cannot specify XML when you specify explicit pivot values using expressions in the pivot_in_clause.

When XML output is generated, the aggregate function is applied to each distinct pivot value, and the database returns a column of XMLType containing an XML string for all value and measure pairs.

The output from this sql is returned as an XMLType and looks like this:

<PivotSet><item><column name = "OWNER">OLAPSYS</column><column name = "OWNER_SEGMENT_COUNT">249</column></item><item><column name = "OWNER">ORDSYS</column><column name = "OWNER_SEGMENT_COUNT">142</column></item><item><column name = "OWNER">QDATA</column><column name = "OWNER_SEGMENT_COUNT">11</column></item><item><column name = "OWNER">SYS</column><column name = "OWNER_SEGMENT_COUNT">2410</column></item><item><column name = "OWNER">SYSMAN</column><column name = "OWNER_SEGMENT_COUNT">1685</column></item><item><column name = "OWNER">SYSTEM</column><column name = "OWNER_SEGMENT_COUNT">742</column></item><item><column name = "OWNER">XDB</column><column name = "OWNER_SEGMENT_COUNT">799</column></item></PivotSet>

What I want to be able to do is run this sql in a subquery_factoring_clause(see next code segment) which will return a XMLType and then select from this value using some XML function(i.e. XMLTable, etc. ) to return the data in a cross-tabular output format as in my first example instead of returning XML data.

with pivot_info as ( 
  select *
   from  ( select owner, count(*) as owner_segment_count
            from  dba_segments
           group by owner )
  pivot xml( sum( owner_segment_count ) as owner_segment_count for owner in( any ) ) )
select ???????
 from  pivot_info,
       XMLTable('/PivotSet/item')

If I can figure out how to do this then this same model can be used over and over for any table where you want the same type of totals/formatting and you do not know in advance the values that should go in the 'pivot in clause'!!!!

tia

[Updated on: Tue, 18 January 2011 14:47]

Report message to a moderator

Re: Flatten output from "pivot xml" [message #489756 is a reply to message #489753]

Tue, 18 January 2011 20:10

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

It might be easier to generate the standard pivot query dynamically without the xml, than to figure out how to flatten the xml data produced by the xml pivot query.

SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> declare
  2    v_sql  varchar2 (32767);
  3  begin
  4    v_sql :=
  5  	 'select *
  6  	  from	 (select owner, count(*) as owner_segment_count
  7  		  from	 dba_segments
  8  		  group  by owner)
  9  	  pivot  (sum (owner_segment_count)
 10  		  for owner in
 11  		    (';
 12    for r in
 13  	 (select distinct owner
 14  	  from	 dba_segments)
 15    loop
 16  	 v_sql := v_sql || '''' ||  r.owner || ''',';
 17    end loop;
 18    v_sql := rtrim (v_sql, ',') || '))';
 19    open :g_ref for v_sql;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> print g_ref

   'MDSYS'    'OUTLN'   'CTXSYS'  'OLAPSYS'       'HR'   'SYSTEM'   'EXFSYS' 'APEX_030200'    'SCOTT' 'APEX_040000'   'DBSNMP'   'ORDSYS'   'SYSMAN'       'OE'       'PM'       'SH'      'XDB'  'ORDDATA'       'IX'      'SYS'    'WMSYS' 'BABOEHME'
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       262          9         80        117         25        392         58           467       9784          1969         26          7        790         77         41        287        885        148         28       2350         49         63

1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Flatten output from "pivot xml" [message #489757 is a reply to message #489756]

Tue, 18 January 2011 20:53

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

The following isn't exactly what you asked for, just some ideas. I have demonstrated that applying xmltable to the pivot_data unpivots it. Using table (xmlsequence) would do the same thing. Then I have tried to extract the pivoted data wihtout using either of those. The problems are that you don't know how many columns to allow for and the column names end up as values in the first row.

-- original query with pivot xml:

SCOTT@orcl_11gR2> with
  2    pivot_info as
  3  	 (select *
  4  	  from	 (select owner, count(*) as owner_segment_count
  5  		  from	 dba_segments
  6  		  group  by owner)
  7  	  pivot xml
  8  		 (sum (owner_segment_count) as owner_segment_count
  9  		  for owner in (any)))
 10  select *
 11  from   pivot_info
 12  /

OWNER_XML
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "OWNER">APEX_030200</column><column name = "OWNER_SEGMENT_COUNT">467</column></item><item><column name = "OWNER">APEX_040000</column><column name = "OWNER_SEGMENT_COUNT">1969</column></item><item><column name = "OWNER">BABOEHME</column><column name = "OWNER_SEGMENT_COU
NT">63</column></item><item><column name = "OWNER">CTXSYS</column><column name = "OWNER_SEGMENT_COUNT">80</column></item><item><column name = "OWNER">DBSNMP</column><column name = "OWNER_SEGMENT_COUNT">26</column></item><item><column name = "OWNER">EXFSYS</column><column name = "OWNER_SEGMENT_COUNT"
>58</column></item><item><column name = "OWNER">HR</column><column name = "OWNER_SEGMENT_COUNT">25</column></item><item><column name = "OWNER">IX</column><column name = "OWNER_SEGMENT_COUNT">28</column></item><item><column name = "OWNER">MDSYS</column><column name = "OWNER_SEGMENT_COUNT">262</column
></item><item><column name = "OWNER">OE</column><column name = "OWNER_SEGMENT_COUNT">77</column></item><item><column name = "OWNER">OLAPSYS</column><column name = "OWNER_SEGMENT_COUNT">117</column></item><item><column name = "OWNER">ORDDATA</column><column name = "OWNER_SEGMENT_COUNT">148</column></
item><item><column name = "OWNER">ORDSYS</column><column name = "OWNER_SEGMENT_COUNT">7</column></item><item><column name = "OWNER">OUTLN</column><column name = "OWNER_SEGMENT_COUNT">9</column></item><item><column name = "OWNER">PM</column><column name = "OWNER_SEGMENT_COUNT">41</column></item><item
><column name = "OWNER">SCOTT</column><column name = "OWNER_SEGMENT_COUNT">9784</column></item><item><column name = "OWNER">SH</column><column name = "OWNER_SEGMENT_COUNT">287</column></item><item><column name = "OWNER">SYS</column><column name = "OWNER_SEGMENT_COUNT">2350</column></item><item><colu
mn name = "OWNER">SYSMAN</column><column name = "OWNER_SEGMENT_COUNT">790</column></item><item><column name = "OWNER">SYSTEM</column><column name = "OWNER_SEGMENT_COUNT">392</column></item><item><column name = "OWNER">WMSYS</column><column name = "OWNER_SEGMENT_COUNT">49</column></item><item><column
 name = "OWNER">XDB</column><column name = "OWNER_SEGMENT_COUNT">885</column></item></PivotSet>


1 row selected.

-- applying xmltable to pivot_info unpivots the data:

SCOTT@orcl_11gR2> with
  2    pivot_info as
  3  	 (select *
  4  	  from	 (select owner, count(*) as owner_segment_count
  5  		  from	 dba_segments
  6  		  group  by owner)
  7  	  pivot xml
  8  		 (sum (owner_segment_count) as owner_segment_count
  9  		  for owner in (any)))
 10  select t.*
 11  from   pivot_info,
 12  	    XMLTable ('/PivotSet/item' passing owner_xml) t
 13  /

COLUMN_VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<item><column name="OWNER">APEX_030200</column><column name="OWNER_SEGMENT_COUNT">467</column></item>
<item><column name="OWNER">APEX_040000</column><column name="OWNER_SEGMENT_COUNT">1969</column></item>
<item><column name="OWNER">BABOEHME</column><column name="OWNER_SEGMENT_COUNT">63</column></item>
<item><column name="OWNER">CTXSYS</column><column name="OWNER_SEGMENT_COUNT">80</column></item>
<item><column name="OWNER">DBSNMP</column><column name="OWNER_SEGMENT_COUNT">26</column></item>
<item><column name="OWNER">EXFSYS</column><column name="OWNER_SEGMENT_COUNT">58</column></item>
<item><column name="OWNER">HR</column><column name="OWNER_SEGMENT_COUNT">25</column></item>
<item><column name="OWNER">IX</column><column name="OWNER_SEGMENT_COUNT">28</column></item>
<item><column name="OWNER">MDSYS</column><column name="OWNER_SEGMENT_COUNT">262</column></item>
<item><column name="OWNER">OE</column><column name="OWNER_SEGMENT_COUNT">77</column></item>
<item><column name="OWNER">OLAPSYS</column><column name="OWNER_SEGMENT_COUNT">117</column></item>
<item><column name="OWNER">ORDDATA</column><column name="OWNER_SEGMENT_COUNT">148</column></item>
<item><column name="OWNER">ORDSYS</column><column name="OWNER_SEGMENT_COUNT">7</column></item>
<item><column name="OWNER">OUTLN</column><column name="OWNER_SEGMENT_COUNT">9</column></item>
<item><column name="OWNER">PM</column><column name="OWNER_SEGMENT_COUNT">41</column></item>
<item><column name="OWNER">SCOTT</column><column name="OWNER_SEGMENT_COUNT">9784</column></item>
<item><column name="OWNER">SH</column><column name="OWNER_SEGMENT_COUNT">287</column></item>
<item><column name="OWNER">SYS</column><column name="OWNER_SEGMENT_COUNT">2350</column></item>
<item><column name="OWNER">SYSMAN</column><column name="OWNER_SEGMENT_COUNT">790</column></item>
<item><column name="OWNER">SYSTEM</column><column name="OWNER_SEGMENT_COUNT">392</column></item>
<item><column name="OWNER">WMSYS</column><column name="OWNER_SEGMENT_COUNT">49</column></item>
<item><column name="OWNER">XDB</column><column name="OWNER_SEGMENT_COUNT">885</column></item>

22 rows selected.

SCOTT@orcl_11gR2> column owner format		    a11
SCOTT@orcl_11gR2> column owner_segment_count format a19
SCOTT@orcl_11gR2> with
  2    pivot_info as
  3  	 (select *
  4  	  from	 (select owner, count(*) as owner_segment_count
  5  		  from	 dba_segments
  6  		  group  by owner)
  7  	  pivot xml
  8  		 (sum (owner_segment_count) as owner_segment_count
  9  		  for owner in (any)))
 10  select extractvalue (value (t), '/item/column[1]') owner,
 11  	    extractvalue (value (t), '/item/column[2]') owner_segment_count
 12  from   pivot_info,
 13  	    XMLTable ('/PivotSet/item' passing owner_xml) t
 14  /

OWNER       OWNER_SEGMENT_COUNT
----------- -------------------
APEX_030200 467
APEX_040000 1969
BABOEHME    63
CTXSYS      80
DBSNMP      26
EXFSYS      58
HR          25
IX          28
MDSYS       262
OE          77
OLAPSYS     117
ORDDATA     148
ORDSYS      7
OUTLN       9
PM          41
SCOTT       9784
SH          287
SYS         2350
SYSMAN      790
SYSTEM      392
WMSYS       49
XDB         885

22 rows selected.

-- attempted extract of pivot_info does not get number of columns and
-- lists column names as values in first row:

SCOTT@orcl_11gR2> column col1 format a11
SCOTT@orcl_11gR2> column col2 format a11
SCOTT@orcl_11gR2> column col3 format a11
SCOTT@orcl_11gR2> column col4 format a11
SCOTT@orcl_11gR2> column col5 format a11
SCOTT@orcl_11gR2> column col6 format a11
SCOTT@orcl_11gR2> with
  2    pivot_info as
  3  	 (select *
  4  	  from	 (select owner, count(*) as owner_segment_count
  5  		  from	 dba_segments
  6  		  group  by owner)
  7  	  pivot xml
  8  		 (sum (owner_segment_count) as owner_segment_count
  9  		  for owner in (any)))
 10  select extractvalue (owner_xml, '/PivotSet/item[1]/column[1]/text()') col1,
 11  	    extractvalue (owner_xml, '/PivotSet/item[2]/column[1]/text()') col2,
 12  	    extractvalue (owner_xml, '/PivotSet/item[3]/column[1]/text()') col3,
 13  	    extractvalue (owner_xml, '/PivotSet/item[4]/column[1]/text()') col4,
 14  	    extractvalue (owner_xml, '/PivotSet/item[5]/column[1]/text()') col5,
 15  	    extractvalue (owner_xml, '/PivotSet/item[6]/column[1]/text()') col6
 16  from   pivot_info
 17  union all
 18  select extractvalue (owner_xml, '/PivotSet/item[1]/column[2]/text()') col1,
 19  	    extractvalue (owner_xml, '/PivotSet/item[2]/column[2]/text()') col2,
 20  	    extractvalue (owner_xml, '/PivotSet/item[3]/column[2]/text()') col3,
 21  	    extractvalue (owner_xml, '/PivotSet/item[4]/column[2]/text()') col4,
 22  	    extractvalue (owner_xml, '/PivotSet/item[5]/column[2]/text()') col5,
 23  	    extractvalue (owner_xml, '/PivotSet/item[6]/column[2]/text()') col6
 24  from   pivot_info
 25  /

COL1        COL2        COL3        COL4        COL5        COL6
----------- ----------- ----------- ----------- ----------- -----------
APEX_030200 APEX_040000 BABOEHME    CTXSYS      DBSNMP      EXFSYS
467         1969        63          80          26          58

2 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Flatten output from "pivot xml" [message #489761 is a reply to message #489757]

Tue, 18 January 2011 22:43

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If you are executing from SQL*Plus, then you can use new_value and a substitution variable to store the owner names and insert them into the standard pivot query, as shown below. This should allow you to put your pivot_info in your with clause, then select from the pivot_info.

SCOTT@orcl_11gR2> set verify off
SCOTT@orcl_11gR2> column owner_vals_col new_value owner_vals
SCOTT@orcl_11gR2> select listagg ('''' || owner || '''', ',')
  2  	      within group (order by owner)
  3  	      as owner_vals_col
  4  from   (select distinct owner
  5  	     from   dba_segments)
  6  /

OWNER_VALS_COL
--------------------------------------------------------------------------------
'APEX_030200','APEX_040000','BABOEHME','CTXSYS','DBSNMP','EXFSYS','HR','IX','MDS
YS','OE','OLAPSYS','ORDDATA','ORDSYS','OUTLN','PM','SCOTT','SH','SYS','SYSMAN','
SYSTEM','WMSYS','XDB'


1 row selected.

SCOTT@orcl_11gR2> with
  2    pivot_info as
  3  	 (select *
  4  	  from	 (select owner,
  5  			 count(*) as owner_segment_count
  6  		  from	 dba_segments
  7  		  group  by owner)
  8  	  pivot
  9  		  (sum (owner_segment_count )
 10  		   for owner in (&owner_vals)))
 11  select *
 12  from   pivot_info
 13  /

'APEX_030200' 'APEX_040000' 'BABOEHME'   'CTXSYS'   'DBSNMP'   'EXFSYS'
------------- ------------- ---------- ---------- ---------- ----------
      'HR'       'IX'    'MDSYS'       'OE'  'OLAPSYS'  'ORDDATA'   'ORDSYS'
---------- ---------- ---------- ---------- ---------- ---------- ----------
   'OUTLN'       'PM'    'SCOTT'       'SH'      'SYS'   'SYSMAN'   'SYSTEM'
---------- ---------- ---------- ---------- ---------- ---------- ----------
   'WMSYS'      'XDB'
---------- ----------
          467          1969         63         80         26         58
        25         28        262         77        117        148          7
         9         41       9784        287       2350        790        392
        49        885


1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490072 is a reply to message #489761]

Thu, 20 January 2011 15:44

spacebar
Messages: 26
Registered: February 2007

Junior Member

Thanks for your time/input!!!

Hopefully Oracle will enhance the 'pivot_in_clause' without XML to permit either a subquery or the wildcard keyword ANY.

This is a variation on one of your examples I came up with:

declare
  pivot_sql           varchar2( 2000 ) :=
    'select *
      from  ( select owner, count(*) as owner_segment_count
               from  dba_segments
              group by owner )
     pivot  ( sum( owner_segment_count ) for owner in(';

  c_quote    constant varchar2(    1 )  :=  '''';

  cursor pivot_items_cur is
    select distinct owner as owner
     from  dba_segments;
  v_pivot_item        varchar2(   30 );
  v_first_pivot_item  boolean       := FALSE;

begin
  dbms_output.enable(null);

  open pivot_items_cur;
  loop
    fetch pivot_items_cur into v_pivot_item;
    exit when pivot_items_cur%NOTFOUND;

    if v_first_pivot_item then
      pivot_sql  :=  pivot_sql || ',';
    end if;

    if not v_first_pivot_item then
      v_first_pivot_item  :=  TRUE;
    end if;

    pivot_sql := pivot_sql || c_quote || v_pivot_item || c_quote;

  end loop;

  pivot_sql  :=  pivot_sql || '))';
  dbms_output.put_line( pivot_sql );

  execute immediate pivot_sql;

end;

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490073 is a reply to message #490072]

Thu, 20 January 2011 15:57

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Your pl/sql block generates the same select statement as my first suggestion, but with more code, less efficiently. Although you display the generated code with dbms_output, no results are produced. Using execute immediate on a select statement by itself does not produce results, which is why I used a ref cursor instead of execute immediate.

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490077 is a reply to message #490073]

Thu, 20 January 2011 19:55

spacebar
Messages: 26
Registered: February 2007

Junior Member

Sorry I forgot to mention, The SQL client I use doesn't support the SQL*Plus syntax that's why
I had to create the anonymous block, and just copy/paste the generated SQL and run it.
Also I forgot and left the 'exec immed' in my post!!!!

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490079 is a reply to message #490077]

Thu, 20 January 2011 20:32

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If you just want to generate the select statement, so that you can copy and paste it, then you can do so with one SQL select statement, without any SQL*Plus features or variables, as shown below.

SCOTT@orcl_11gR2> select 'select *
  2  	     from   (select owner,
  3  			    count(*) as owner_segment_count
  4  		     from   dba_segments
  5  		     group  by owner)
  6  	     pivot
  7  		    (sum (owner_segment_count )
  8  		     for owner in (' ||
  9  			   listagg ('''' || owner || '''', ',')
 10  			     within group (order by owner) || '))'
 11  from   (select distinct owner
 12  	     from   dba_segments)
 13  /

'SELECT*FROM(SELECTOWNER,COUNT(*)ASOWNER_SEGMENT_COUNTFROMDBA_SEGMENTSGROUPBYOWN
--------------------------------------------------------------------------------
select *
        from   (select owner,
 	               count(*) as owner_segment_count
                from   dba_segments
                group  by owner)
        pivot
               (sum (owner_segment_count )
                for owner in ('APEX_030200','APEX_040000','BABOEHME','CTXSYS','D
BSNMP','EXFSYS','HR','IX','MDSYS','OE','OLAPSYS','ORDDATA','ORDSYS','OUTLN','PM'
,'SCOTT','SH','SYS','SYSMAN','SYSTEM','WMSYS','XDB'))


1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490241 is a reply to message #490079]

Sat, 22 January 2011 15:54

spacebar
Messages: 26
Registered: February 2007

Junior Member

Thanks! I'll have to keep that in my examples, Currently we're on release 11.1.

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490244 is a reply to message #490241]

Sat, 22 January 2011 16:54

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I forgot that listagg was introduced in 11g release 2. You can use wm_concat instead, as shown below, but it is not documented or supported. There are also various other string aggregation techniques.

SCOTT@orcl_11gR2> select 'select *
  2  	     from   (select owner,
  3  			    count(*) as owner_segment_count
  4  		     from   dba_segments
  5  		     group  by owner)
  6  	     pivot
  7  		    (sum (owner_segment_count )
  8  		     for owner in (' ||
  9  			   wm_concat ('''' || owner || '''') || '))'
 10  from   (select distinct owner
 11  	     from   dba_segments)
 12  /

'SELECT*FROM(SELECTOWNER,COUNT(*)ASOWNER_SEGMENT_COUNTFROMDBA_SEGMENTSGROUPBYOWN
--------------------------------------------------------------------------------
select *
        from   (select owner,
 	               count(*) as owner_segment_count
                from   dba_segments
                group  by owner)
        pivot
               (sum (owner_segment_count )
                for owner in ('MDSYS','OUTLN','CTXSYS','OLAPSYS','HR','SYSTEM','
EXFSYS','APEX_030200','SCOTT','APEX_040000','DBSNMP','ORDSYS','SYSMAN','OE','PM'
,'SH','XDB','ORDDATA','IX','SYS','WMSYS','BABOEHME'))


1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490245 is a reply to message #490244]

Sat, 22 January 2011 19:31

spacebar
Messages: 26
Registered: February 2007

Junior Member

Well since I don't have "wm_concat" either!!!

I've came up with this which also does it:

with
sql_stmt as(
  select 'select *
           from  ( select owner, count(*) as owner_segment_count
                    from   dba_segments
                   group by owner )
           pivot ( sum( owner_segment_count ) for owner in (' as sql
   from dual ),
pivot_data as(
  select replace(rtrim(xmlagg(xmlelement(c,owner,',').extract('//text()')),','), '&apos;', '''') || '))' as pivot_data
   from  ( select distinct '''' || owner || '''' as owner
            from   dba_segments
           order by owner ) )
select sql || pivot_data
 from  sql_stmt join pivot_data on sql != pivot_data

Report message to a moderator

Re: Flatten output from "pivot xml" [message #490246 is a reply to message #490245]

Sat, 22 January 2011 19:54

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If you are using 11.1, then you should have wm_concat, but using xmlagg is probably better, since it is supported.

Report message to a moderator

Previous Topic:	How to reflect xml changes without restart tomcat
Next Topic:	jquery slideToggle doesn't work in IE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 20:21:25 CST 2024