This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,715 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

A simple output pivot option for sqlplus (or SQLcl)

Martin Preiss
Martin Preiss amateur byzantinistSaarbrückenMember 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 amateur byzantinist SaarbrückenMember 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 amateur byzantinist SaarbrückenMember 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 Chief Build Breaker 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 amateur byzantinist SaarbrückenMember 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,261 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 amateur byzantinist SaarbrückenMember 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.