Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, vikram.

Asked: September 24, 2019 - 5:15 pm UTC

Last updated: January 13, 2022 - 12:17 am UTC

Version: 11g

Viewed 1000+ times

You Asked

We have a table which contains db_name and usernames. In the output we need list of users per DB i.e. number of columns will be equal to distinct db_name.

sample output format:
DB1 DB2
USER1 USER4
USER2 USER5
USER3

Database version: 11g


Below is sample script for reference:

CREATE TABLE app_users (
    username   VARCHAR2(40),
    db_name    VARCHAR2(40)
);

insert all
into app_users values ('USER1','DB1')
into app_users values ('USER2','DB1')
into app_users values ('USER3','DB1')
into app_users values ('USER4','DB2')
into app_users values ('USER5','DB2')
select * from dual;


Please help.

and Connor said...

We don't have dynamic pivot currently, but the good folks at AMIS wrote a nice dynamic pivot function a while back, details here:

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

which can be used to achieve what you want. Once created, you only need the last select :-)

SQL> CREATE OR REPLACE
  2  type PivotImpl as object
  3  (
  4    ret_type anytype,      -- The return type of the table function
  5    stmt varchar2(32767),
  6    fmt  varchar2(32767),
  7    cur integer,
  8    static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
  9    return number,
 10    static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
 11    return number,
 12    static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
 13    return number,
 14    member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
 15    return number,
 16    member function ODCITableClose( self in PivotImpl )
 17    return number
 18  )
 19  /

Type created.

SQL>
SQL> create or replace type body PivotImpl as
  2    static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
  3    return number
  4    is
  5      atyp anytype;
  6      cur integer;
  7      numcols number;
  8      desc_tab dbms_sql.desc_tab2;
  9      rc sys_refcursor;
 10      t_c2 varchar2(32767);
 11      t_fmt varchar2(1000);
 12    begin
 13      cur := dbms_sql.open_cursor;
 14      dbms_sql.parse( cur, p_stmt, dbms_sql.native );
 15      dbms_sql.describe_columns2( cur, numcols, desc_tab );
 16      dbms_sql.close_cursor( cur );
 17  --
 18      anytype.begincreate( dbms_types.typecode_object, atyp );
 19  for i in 1 .. numcols - 2
 20  loop
 21        atyp.addattr( desc_tab( i ).col_name
 22                    , case desc_tab( i ).col_type
 23                        when 1   then dbms_types.typecode_varchar2
 24                        when 2   then dbms_types.typecode_number
 25                        when 9   then dbms_types.typecode_varchar2
 26                        when 11  then dbms_types.typecode_varchar2  -- show rowid as varchar2
 27                        when 12  then dbms_types.typecode_date
 28                        when 208 then dbms_types.typecode_varchar2  -- show urowid as varchar2
 29                        when 96  then dbms_types.typecode_char
 30                        when 180 then dbms_types.typecode_timestamp
 31                        when 181 then dbms_types.typecode_timestamp_tz
 32                        when 231 then dbms_types.typecode_timestamp_ltz
 33                        when 182 then dbms_types.typecode_interval_ym
 34                        when 183 then dbms_types.typecode_interval_ds
 35                      end
 36                    , desc_tab( i ).col_precision
 37                    , desc_tab( i ).col_scale
 38                    , case desc_tab( i ).col_type
 39                        when 11 then 18  -- for rowid col_max_len = 16, and 18 characters are shown
 40                        else desc_tab( i ).col_max_len
 41                      end
 42                    , desc_tab( i ).col_charsetid
 43                    , desc_tab( i ).col_charsetform
 44                    );
 45  end loop;
 46      if instr( p_fmt, '@p@' ) > 0
 47      then
 48        t_fmt := p_fmt;
 49      else
 50        t_fmt := '@p@';
 51      end if;
 52      open rc for replace( 'select distinct ' || t_fmt || '
 53                        from( ' || p_stmt || ' )
 54            order by ' || t_fmt
 55     , '@p@'
 56                         , desc_tab( numcols - 1 ).col_name
 57     );
 58  loop
 59        fetch rc into t_c2;
 60    exit when rc%notfound;
 61        atyp.addattr( t_c2
 62                    , case desc_tab( numcols ).col_type
 63                      when 1   then dbms_types.typecode_varchar2
 64                      when 2   then dbms_types.typecode_number
 65                      when 9   then dbms_types.typecode_varchar2
 66                      when 11  then dbms_types.typecode_varchar2  -- show rowid as varchar2
 67                      when 12  then dbms_types.typecode_date
 68                      when 208 then dbms_types.typecode_urowid
 69                      when 96  then dbms_types.typecode_char
 70                      when 180 then dbms_types.typecode_timestamp
 71                      when 181 then dbms_types.typecode_timestamp_tz
 72                      when 231 then dbms_types.typecode_timestamp_ltz
 73                      when 182 then dbms_types.typecode_interval_ym
 74                      when 183 then dbms_types.typecode_interval_ds
 75                    end
 76                  , desc_tab( numcols ).col_precision
 77                  , desc_tab( numcols ).col_scale
 78                  , case desc_tab( numcols ).col_type
 79                      when 11 then 18  -- for rowid col_max_len = 16, and 18 characters are shown
 80                      else desc_tab( numcols ).col_max_len
 81                    end
 82                  , desc_tab( numcols ).col_charsetid
 83                  , desc_tab( numcols ).col_charsetform
 84                    );
 85      end loop;
 86  close rc;
 87      atyp.endcreate;
 88      anytype.begincreate( dbms_types.typecode_table, rtype );
 89      rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
 90      rtype.endcreate();
 91      return odciconst.success;
 92    exception
 93      when others then
 94        return odciconst.error;
 95    end;
 96  --
 97    static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
 98    return number
 99    is
100      prec     pls_integer;
101      scale    pls_integer;
102      len      pls_integer;
103      csid     pls_integer;
104      csfrm    pls_integer;
105      elem_typ anytype;
106      aname    varchar2(30);
107      tc       pls_integer;
108    begin
109      tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
110  --
111      if instr( p_fmt, '@p@' ) > 0
112      then
113        sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );
114      else
115        sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
116      end if;
117      return odciconst.success;
118    end;
119  --
120    static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
121    return number
122    is
123      cur         integer;
124      numcols     number;
125      desc_tab    dbms_sql.desc_tab2;
126      t_stmt      varchar2(32767);
127      type_code   pls_integer;
128      prec        pls_integer;
129      scale       pls_integer;
130      len         pls_integer;
131      csid        pls_integer;
132      csfrm       pls_integer;
133      schema_name varchar2(30);
134      type_name   varchar2(30);
135      version     varchar2(30);
136      attr_count  pls_integer;
137      attr_type   anytype;
138      attr_name   varchar2(100);
139      dummy2      integer;
140    begin
141      cur := dbms_sql.open_cursor;
142      dbms_sql.parse( cur, p_stmt, dbms_sql.native );
143      dbms_sql.describe_columns2( cur, numcols, desc_tab );
144      dbms_sql.close_cursor( cur );
145  --
146  for i in 1 .. numcols - 2
147  loop
148        t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
149  end loop;
150  --
151      type_code := sctx.ret_type.getinfo( prec
152                                        , scale
153                                        , len
154                                        , csid
155                                        , csfrm
156                                        , schema_name
157                                        , type_name
158                                        , version
159                                        , attr_count
160                                        );
161      for i in numcols - 1 .. attr_count
162      loop
163        type_code := sctx.ret_type.getattreleminfo( i
164                                                   , prec
165                                                   , scale
166                                                   , len
167                                                   , csid
168                                                   , csfrm
169                                                   , attr_type
170                                                   , attr_name
171                                                   );
172        t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
173                               , '@p@'
174                           , desc_tab( numcols - 1 ).col_name
175             );
176  end loop;
177  t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
178  for i in 1 .. numcols - 2
179  loop
180    if i = 1
181    then
182          t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
183    else
184          t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
185    end if;
186  end loop;
187  --
188  dbms_output.put_line( t_stmt );
189      sctx.cur := dbms_sql.open_cursor;
190      dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
191      for i in 1 .. attr_count
192      loop
193        type_code := sctx.ret_type.getattreleminfo( i
194                                                   , prec
195                                                   , scale
196                                                   , len
197                                                   , csid
198                                                   , csfrm
199                                                   , attr_type
200                                                   , attr_name
201                                                   );
202        case type_code
203          when dbms_types.typecode_char          then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
204          when dbms_types.typecode_varchar2      then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
205          when dbms_types.typecode_number        then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );
206          when dbms_types.typecode_date          then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );
207          when dbms_types.typecode_urowid        then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );
208          when dbms_types.typecode_timestamp     then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );
209          when dbms_types.typecode_timestamp_tz  then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );
210          when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );
211          when dbms_types.typecode_interval_ym   then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );
212          when dbms_types.typecode_interval_ds   then dbms_sql.define_column( sctx.cur, i, cast( null as interval day to second ) );
213        end case;
214  end loop;
215      dummy2 := dbms_sql.execute( sctx.cur );
216      return odciconst.success;
217    end;
218  --
219    member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
220    return number
221    is
222      c1_col_type pls_integer;
223      type_code   pls_integer;
224      prec        pls_integer;
225      scale       pls_integer;
226      len         pls_integer;
227      csid        pls_integer;
228      csfrm       pls_integer;
229      schema_name varchar2(30);
230      type_name   varchar2(30);
231      version     varchar2(30);
232      attr_count  pls_integer;
233      attr_type   anytype;
234      attr_name   varchar2(100);
235      v1     varchar2(32767);
236      n1     number;
237      d1     date;
238      ur1    urowid;
239      ids1   interval day to second;
240      iym1   interval year to month;
241      ts1    timestamp;
242      tstz1  timestamp with time zone;
243      tsltz1 timestamp with local time zone;
244    begin
245      outset := null;
246      if nrows < 1
247      then
248  -- is this possible???
249        return odciconst.success;
250      end if;
251  --
252  dbms_output.put_line( 'fetch' );
253      if dbms_sql.fetch_rows( self.cur ) = 0
254      then
255        return odciconst.success;
256      end if;
257  --
258  dbms_output.put_line( 'done' );
259      type_code := self.ret_type.getinfo( prec
260                                        , scale
261                                        , len
262                                        , csid
263                                        , csfrm
264                                        , schema_name
265                                        , type_name
266                                        , version
267                                        , attr_count
268                                        );
269      anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
270      outset.addinstance;
271      outset.piecewise();
272      for i in 1 .. attr_count
273      loop
274        type_code := self.ret_type.getattreleminfo( i
275                                                   , prec
276                                                   , scale
277                                                   , len
278                                                   , csid
279                                                   , csfrm
280                                                   , attr_type
281                                                   , attr_name
282                                                   );
283  dbms_output.put_line( attr_name );
284          case type_code
285            when dbms_types.typecode_char then
286              dbms_sql.column_value( self.cur, i, v1 );
287              outset.setchar( v1 );
288            when dbms_types.typecode_varchar2 then
289              dbms_sql.column_value( self.cur, i, v1 );
290              outset.setvarchar2( v1 );
291            when dbms_types.typecode_number then
292              dbms_sql.column_value( self.cur, i, n1 );
293              outset.setnumber( n1 );
294            when dbms_types.typecode_date then
295              dbms_sql.column_value( self.cur, i, d1 );
296              outset.setdate( d1 );
297            when dbms_types.typecode_urowid then
298              dbms_sql.column_value( self.cur, i, ur1 );
299              outset.seturowid( ur1 );
300            when dbms_types.typecode_interval_ds then
301              dbms_sql.column_value( self.cur, i, ids1 );
302
303      outset.setintervalds( ids1 );
304            when dbms_types.typecode_interval_ym then
305              dbms_sql.column_value( self.cur, i, iym1 );
306              outset.setintervalym( iym1 );
307            when dbms_types.typecode_timestamp then
308              dbms_sql.column_value( self.cur, i, ts1 );
309              outset.settimestamp( ts1 );
310            when dbms_types.typecode_timestamp_tz then
311              dbms_sql.column_value( self.cur, i, tstz1 );
312              outset.settimestamptz( tstz1 );
313            when dbms_types.typecode_timestamp_ltz then
314              dbms_sql.column_value( self.cur, i, tsltz1 );
315              outset.settimestampltz( tsltz1 );
316          end case;
317      end loop;
318      outset.endcreate;
319      return odciconst.success;
320    end;
321  --
322    member function ODCITableClose( self in PivotImpl )
323    return number
324    is
325      c integer;
326    begin
327      c := self.cur;
328      dbms_sql.close_cursor( c );
329      return odciconst.success;
330    end;
331  end;
332  /

Type body created.

SQL>
SQL> create or replace
  2  function pivot( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
  3  return anydataset pipelined using PivotImpl;
  4  /

Function created.

SQL> CREATE TABLE app_users (
  2      username   VARCHAR2(40),
  3      db_name    VARCHAR2(40)
  4  );

Table created.

SQL>
SQL> insert all
  2  into app_users values ('USER1','DB1')
  3  into app_users values ('USER2','DB1')
  4  into app_users values ('USER3','DB1')
  5  into app_users values ('USER4','DB2')
  6  into app_users values ('USER5','DB2')
  7  select * from dual;

5 rows created.

SQL>
SQL>

SQL> select * from table(pivot('select row_number() over ( partition by db_name order by username) as x, db_name, username from app_users'));

         X DB1                                      DB2
---------- ---------------------------------------- ----------------------------------------
         1 USER1                                    USER4
         2 USER2                                    USER5
         3 USER3
         


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you trying to understand the solution :)

vikram chandel, September 25, 2019 - 5:06 pm UTC

Thank you. I am trying to understand overall query :) not an easy one though.

Somehow if we can decide the number of columns i.e. DB_NAME field values well in advance. Then how can we get list of users within each columns using pivot query. Please assist.

DB1 DB2
USER1 USER4
USER2 USER5
USER3
Chris Saxon
September 26, 2019 - 10:34 am UTC

The database adds an implicit group by for any columns in the table not in the PIVOT clause.

So calculate a row_number() in a subquery for each db_name. And take the min username for each number:

with rws as (
  select a.*,
         row_number () over ( 
           partition by db_name 
           order by username 
         ) rn
  from   app_users a
)
  select * from rws
  pivot (
    min ( username ) for db_name in (
      'DB1', 'DB2'
    )
  );

RN   'DB1'   'DB2'    
    1 USER1    USER4     
    2 USER2    USER5     
    3 USER3    <null>    


If you want to know more about (un)pivot, read https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

Dynamic Pivot

Rajeshwaran, Jeyabal, September 26, 2019 - 12:42 pm UTC

Another approach for Dynamic Pivot using a bit of PL/SQL like this

demo@PDB1> create or replace function generic_pivot(
  2     p_source in varchar2,
  3     p_pivotfor in varchar2,
  4     p_aggfns in varchar2
  5  )
  6  return sys_refcursor as
  7     l_sql dbms_sql.Varchar2_Table ;
  8     l_result sys_refcursor;
  9     l_pivot_in_list long;
 10  begin
 11     l_sql(1) := q'$ select listagg(''''||#P_PIVOTFOR# ||''' as "'||#P_PIVOTFOR#||'"',',') within group ( order by #P_PIVOTFOR# )
 12                     from ( select distinct #P_PIVOTFOR# from #P_SOURCE# ) $' ;
 13     l_sql(2) := q'$ select * from #P_SOURCE#
 14                                     pivot( #P_AGGFNS#
 15                                             for #P_PIVOTFOR# in (#PIVOT_IN_LIST#) ) order by 1,2 $';
 16
 17     l_sql(1) := replace( l_sql(1) ,'#P_PIVOTFOR#',p_pivotfor);
 18     l_sql(1) := replace( l_sql(1) ,'#P_SOURCE#',p_source);
 19
 20     execute immediate l_sql(1) into l_pivot_in_list;
 21
 22     l_sql(2) := replace( l_sql(2),'#P_SOURCE#',p_source);
 23     l_sql(2) := replace( l_sql(2),'#P_AGGFNS#',p_aggfns);
 24     l_sql(2) := replace( l_sql(2),'#P_PIVOTFOR#',p_pivotfor);
 25     l_sql(2) := replace( l_sql(2),'#PIVOT_IN_LIST#',l_pivot_in_list);
 26
 27
 28     open l_result for l_sql(2);
 29     return l_result;
 30  end;
 31  /

Function created.

demo@PDB1>
demo@PDB1> variable l_sql varchar2(150)
demo@PDB1> begin
  2     :l_sql := ' ( select db_name,username, '||
  3             ' row_number() over(partition by'||
  4             ' db_name order by username) r '||
  5             ' from app_users ) ';
  6  end;
  7  /

PL/SQL procedure successfully completed.

demo@PDB1>
demo@PDB1> variable x refcursor
demo@PDB1> exec :x := generic_pivot( :l_sql,'db_name','max(username)' );

PL/SQL procedure successfully completed.

demo@PDB1> print x

         R DB1       DB2
---------- --------- --------
         1 USER1     USER4
         2 USER2     USER5
         3 USER3

demo@PDB1>

Chris Saxon
September 26, 2019 - 2:31 pm UTC

Hmmm, string replacement. I smell SQL injection!

Thank you

vikram chandel, September 26, 2019 - 7:25 pm UTC

Thank for answering our doubts team.

on SQL Injection part.

Rajeshwaran, Jeyabal, September 27, 2019 - 2:25 am UTC

...Hmmm, string replacement. I smell SQL injection! ....

But that is just an idea, once should take and sanitize the input using DBMS_ASSERT to be free from SQL Injection.

Requesting this for postgres

Siddheshwar, January 12, 2022 - 10:52 am UTC

Hi,
I have implemented this for our db, now we are migrating to postgres.

unfortunately , nothing found like this in PG.

Requesting your help to get PostgreSQL version for this .


Connor McDonald
January 13, 2022 - 12:17 am UTC

You'd need to find a postgres SQL forum for that :-)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.