Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
A simple output pivot option for sqlplus (or SQLcl)

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