Forum Stats

  • 3,824,937 Users
  • 2,260,441 Discussions
  • 7,896,354 Comments

Discussions

A simple output pivot option for sqlplus (or SQLcl)

Martin Preiss
Martin Preiss Member Posts: 2,381 Gold Trophy
edited Jan 11, 2016 6:01PM in Database Ideas - Ideas

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.

Martin PreissAntonio NavarroKiran PawarManish Chaturvedictriebabhinivesh.jainPravin Takpire2887088Lothar FlatzborneselZlatko SirotichimmyAish13top.gunWilliam RobertsonApexBineDear DBA FrankN.B.sensoftUser_I4NUNRostislav Kushnirenko
25 votes

Active · Last Updated

Comments

  • Why not to leverage SQL pivot syntax?

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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.

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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
    top.gun Member Posts: 3,666 Gold Crown

    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
    Kris Rice-Oracle Posts: 1,393 Employee

    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"

    Screen Shot 2015-10-14 at 18.14.29.png

    Martin PreissberxApexBineUser_I4NUN
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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"

    Screen Shot 2015-10-14 at 18.14.29.png

    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!

    ApexBine
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,256 Employee
    edited Oct 15, 2015 4:51PM

    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
    Martin Preiss Member Posts: 2,381 Gold Trophy

    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.