Database Software

Why not to leverage SQL pivot syntax?

I see this more as a display option than as a transformation of the result set - but that said: if it was possible to get this result with the pivot clause and a default setting (so we don't need to do much typing) this would be also sufficient.

The in-built Oracle pivot function is here:

http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG0209

The in-built Oracle pivot function is here:

http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#DWHSG0209

absolutely - but I don't see how this could allow a pivoting of the results on record level as it is shown in my example without a lot more typing than just "\x". The idea is not about doing complex transformations of result sets - but just to change the basic display strategy: as Tom Kyte's print_table does it: the function takes a query and represents the result in a lot of rows instead of showing a lot of columns; and sometimes this is useful - for example when you try to analyse the content of v$sql or something like that and don't want to write a query to filter the relevant columns.

absolutely - but I don't see how this could allow a pivoting of the results on record level as it is shown in my example without a lot more typing than just "\x". The idea is not about doing complex transformations of result sets - but just to change the basic display strategy: as Tom Kyte's print_table does it: the function takes a query and represents the result in a lot of rows instead of showing a lot of columns; and sometimes this is useful - for example when you try to analyse the content of v$sql or something like that and don't want to write a query to filter the relevant columns.

Your idea is really for a short-hand (or alter session) pivot switch.

With the new drop yesterday that adds scripting support you can do this now with just a few lines of code.

Create a new file named printtab.js and put this in it

var sql="";

for(var i=1;i<args.length;i++){

sql = sql + " " + args[i];

}

ctx.write(sql + "\n\n");

var ret = util.executeReturnListofList(sql,null);

for (var i = 0; i < ret.size(); i++) {

ctx.write('>ROW \\n');

for( var ii=0;ii\<ret\[i\].size();ii++) {

    ctx.write("\\t" + ret\[0\]\[ii\] + " : " + ret\[i\]\[ii\] + "\\n");

}

}

ctx.write('\n\n');

Then just prefix your sql with "script printtab"

With the new drop yesterday that adds scripting support you can do this now with just a few lines of code.

Create a new file named printtab.js and put this in it

var sql="";

for(var i=1;i<args.length;i++){

sql = sql + " " + args[i];

}

ctx.write(sql + "\n\n");

var ret = util.executeReturnListofList(sql,null);

for (var i = 0; i < ret.size(); i++) {

ctx.write('>ROW \\n');

for( var ii=0;ii\<ret\[i\].size();ii++) {

    ctx.write("\\t" + ret\[0\]\[ii\] + " : " + ret\[i\]\[ii\] + "\\n");

}

}

ctx.write('\n\n');

Then just prefix your sql with "script printtab"

Thank you, Kris! That's great. I would love to see it as a builtin function - but having the script is almost as good. SQLcl is just an awesome tool - and you do a great job in its development!

SELECT

  *

FROM (

    (SELECT

      ROWNUM,

      TO_CHAR(DEPTNO) DEPTNO,

      DNAME ,

      LOC

    FROM

      scott.dept

    )  UNPIVOT (

       column_value FOR col IN (DEPTNO, DNAME, LOC)

    )

);

It is more verbose, but more flexible too. It is up to the tool to make writing complex SQL query easy...

SELECT

  *

FROM (

    (SELECT

      ROWNUM,

      TO_CHAR(DEPTNO) DEPTNO,

      DNAME ,

      LOC

    FROM

      scott.dept

    )  UNPIVOT (

       column_value FOR col IN (DEPTNO, DNAME, LOC)

    )

);

It is more verbose, but more flexible too. It is up to the tool to make writing complex SQL query easy...

certainly. And as long as I have only 3 columns in my IN list I wouldn't even complain about writing this without tool support (though there would be no need for UNPIVOT with a small number of columns) - but for v$sql I would rather try to avoid this. Of course a fitting tool could do the job but I don't think I have seen a tool that allows to add all the columns of a table/result set to the IN clause of UNPIVOT with a single click or drag&drop. And even if this tool exists I am not sure if I will find it on every server I have to work with (while I would not be surprised to find sqlplus or sqlcl there).

you may want to start the loop from "var i = 1". "ret[0]" is a row of column names.

Kris

I followed your instruction and in SQL Developer 23.1.1.339 SQL Editor and SQL Developer VS code plugin, both on Windows machine, it did not work.

With SQLcl, what about the a SQL statement being very long. Generally it is a multiple line statement and also include string filtering condition (single quote required). Could you show an example?

In SQL Developer Editor, it did not error out but did not display any output after I select my statement and press F5.

In VS code plugin,

SQL> show version;
Oracle SQLDeveloper Command-Line (SQLcl) version: 23.3.1.0 build: 23.3.1.11.1527
SQL> script C:\_local\_scripts\myscripts\sqlcl\printtab select username, locked_date from dba_users where oracle_maintained='Y';
!ScriptCommand.1!SQL>
SQL> script "C:\_local\_scripts\myscripts\sqlcl\printtab" "select username, locked_date from dba_users where oracle_maintained='Y';"
!ScriptCommand.1!SQL>
script "C:\_local\_scripts\myscripts\sqlcl\printtab.js" select username, locked_date
from dba_users where oracle_maintained='Y';

SQL> script "C:\_local\_scripts\myscripts\sqlcl\printtab.js" select username, locked_date \
!ScriptCommand.1!SQL> from dba_users where oracle_maintained='Y';

Error starting at line : 1 in command -
from dba_users where oracle_maintained='Y'
Error report -
Unknown Command

Best,

Kevin