Thread: rotate psql output

I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'.
Is this possible? I have had no luck searching for this as rotate usually means log rotation.
BC

Ben Carbery wrote:
> I have a query that returns many columns but few rows. I would like to
> display output horizontally instead of vertically, i.e. rotating by 90
> degress, so column headings appear in the left margin, and the output is
> not 'wrapped'.
> Is this possible? I have had no luck searching for this as rotate
> usually means log rotation.
> BC


Have you tried \x ?

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

On Thu, 2010-09-30 at 21:17 +1000, Ben Carbery wrote:
> I have a query that returns many columns but few rows. I would like to
> display output horizontally instead of vertically, i.e. rotating by 90
> degress, so column headings appear in the left margin, and the output
> is not
> 'wrapped'.
> Is this possible?

psql -x ?

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

Hadn't tried that. Actually it's much better, but each record is displayed separately, so visually comparing 2 or 3 records is not possible.

Strange if this can't be done, I would have thought it a common request!

2010/9/30 Ben Carbery <ben.carbery@gmail.com>:
> Strange if this can't be done, I would have thought it a common request!

Just curiosity. Is there any other DB capable of such a thing?

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

On 30/09/2010 12:17, Ben Carbery wrote:
> I have a query that returns many columns but few rows. I would like to
> display output horizontally instead of vertically, i.e. rotating by 90
> degress, so column headings appear in the left margin, and the output is
> not 'wrapped'.
> Is this possible? I have had no luck searching for this as rotate
> usually means log rotation.

Googling on "sql swap rows columns" found this:

http://stackoverflow.com/questions/584232/t-sql-how-to-swap-rows-and-columns

...which you'll be able to adapt.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

2010/9/30 Raymond O'Donnell <rod@iol.ie>:
> On 30/09/2010 12:17, Ben Carbery wrote:
> Googling on "sql swap rows columns" found this:
>
> http://stackoverflow.com/questions/584232/t-sql-how-to-swap-rows-and-columns
>
> ...which you'll be able to adapt.

We also have the fantastic crosstab in tablefunc module (see chapter
F.36.1.4 for v9.0.0)

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

..but these seem to be more pivot table functions that alter the data, rather than displaying it differently. Not sure if this method is adaptable but I may have a go.

To me swapping would be better suited as an option to psql. After all, calling columns 'columns' in sql is aribtrary - they could just as easily have been called rows or something else, so there's no reason for the output to be fixed that way. Although that would chew a lot of memory if you have a lot of records as you would need to read every record before formatting..and I guess that explains why it hasn't been done.

No idea if this can be done in other clients!

2010/9/30 Ben Carbery <ben.carbery@gmail.com>:
> On Thu, Sep 30, 2010 at 9:36 PM, Vincenzo Romano
> <vincenzo.romano@notorand.it> wrote:
>>
>> We also have the fantastic crosstab in tablefunc module (see chapter
>> F.36.1.4 for v9.0.0)
>>
> ..but these seem to be more pivot table functions that alter the data,
> rather than displaying it differently. Not sure if this method is adaptable
> but I may have a go.

Alter? crosstab doesn't alter anything if you don't ask to.

> To me swapping would be better suited as an option to psql. After all,
> calling columns 'columns' in sql is aribtrary - they could just as easily
> have been called rows or something else, so there's no reason for the output
> to be fixed that way. Although that would chew a lot of memory if you have a
> lot of records as you would need to read every record before formatting..and
> I guess that explains why it hasn't been done.
>
> No idea if this can be done in other clients!

Actually none, AFAIK.

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

On 30/09/2010 12:31, Vincenzo Romano wrote:
> 2010/9/30 Ben Carbery <ben.carbery@gmail.com>:
>> Strange if this can't be done, I would have thought it a common request!
>
> Just curiosity. Is there any other DB capable of such a thing?
>

MySQL does this using an alternate end-of-statement character:

';' or '\g' gives the usual orientation,

'\G' gives the rotated orientation.

    Cheers,

    Matthew

--
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                  Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
JID: matthew@infracaninophile.co.uk               Kent, CT11 9PW

On 09/30/2010 02:11 PM, Matthew Seaman wrote:
> On 30/09/2010 12:31, Vincenzo Romano wrote:
>> 2010/9/30 Ben Carbery<ben.carbery@gmail.com>:
>>> Strange if this can't be done, I would have thought it a common request!
>>
>> Just curiosity. Is there any other DB capable of such a thing?
>>
>
> MySQL does this using an alternate end-of-statement character:
>
> ';' or '\g' gives the usual orientation,
>
> '\G' gives the rotated orientation.


No, that is exactly the same as \x or psql -x does.

--
Tommy Gildseth

For the record, I did find a way to do with another application.. mathematica makes it fairly trivial:

Say a query such as:
data = SQLSelect[someconnection,"sometable"]

Printing it:
TableForm[data]

Then simply:
Transpose[TableForm[data]]