A simple output pivot option for sqlplus (or SQLcl)

Martin Preiss

In postgres' command line client (psql) there is a nice output pivoting switch to display records in a vertical view:

select * from t;

 a | b | c
---+---+---
 1 | 2 | 3
 4 | 5 | 6



--> Switch display with \x


select * from t;


-[ RECORD 1 ]
a | 1
b | 2
c | 3
-[ RECORD 2 ]
a | 4
b | 5
c | 6

This would make data analysis simpler for result sets with many columns. Of course there are some useful helper functions (Tom Kyte's print_table, Tanel Poder's printtab), but I would prefer a simple built-in mechanism providing this.

Vadim Tropashko-Oracle

Why not to leverage SQL pivot syntax?

Martin Preiss

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.

top.gun

The in-built Oracle pivot function is here:

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

Martin Preiss

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.

top.gun

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.

Kris Rice-Oracle

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"

Martin Preiss

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!

Vadim Tropashko-Oracle

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...

Martin Preiss

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).

User_I4NUN

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

https://community.oracle.com/tech/apps-infra/discussion/comment/16654349#Comment_16654349