Thread

1. Re: Cross-Tab queries in postgres?

   wsheldah@lexmark.com — 2002-07-22T13:54:00Z

   
   
   In MS Access, crosstab queries are implemented with the keyword TRANSFORM, IIRC.
   The easiest way to figure them out is to build a cross-tab query with the GUI
   query builder, then look at the SQL it produces. I think this might be
   implemented in SQL Server as well. It can be handy, but if there's not something
   comparable in the SQL standard, I'd rather see several other features added long
   before this one. Just my two cents.
   
   Wes Sheldahl
   
   
   
   
   Joe Conway <mail%joeconway.com@interlock.lexmark.com> on 07/22/2002 03:09:42 AM
   
   To:   Martijn van Oosterhout <kleptog%svana.org@interlock.lexmark.com>
   cc:   Postgresql General <pgsql-general%postgresql.org@interlock.lexmark.com>
         (bcc: Wesley Sheldahl/Lex/Lexmark)
   Subject:  Re: [GENERAL] Cross-Tab queries in postgres?
   
   
   Martijn van Oosterhout wrote:
   > I know they're not supported and that they should be done in the
   > presentation end of the software. However, I have a case where I need to use
   > the result as the input to another query. So I'm reading the output, doing
   > the cross-tab and copying the result back into the database.
   >
   
   [snip]
   
   > Anyway, it doesn't seem to hard to implement so I was wondering if any other
   > database systems actually implement it. Mostly I'm interested in what syntax
   > they use to indicate such a query. (I presume it's not in the standard or
   > it'd be there already).
   
   I haven't seen this except in MS Access. I don't think you can directly
   produce a crosstab in MS SQL Server or Oracle, although in Oracle you
   can build your own table function.
   
   
   
   ---------------------------(end of broadcast)---------------------------
   TIP 2: you can get off all lists at once with the unregister command
       (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   
   
   
   
   
   

2. Re: Cross-Tab queries in postgres?

   Joe Conway <mail@joeconway.com> — 2002-07-23T04:21:29Z

   wsheldah@lexmark.com wrote:
   > In MS Access, crosstab queries are implemented with the keyword TRANSFORM, IIRC.
   > The easiest way to figure them out is to build a cross-tab query with the GUI
   > query builder, then look at the SQL it produces. I think this might be
   > implemented in SQL Server as well. It can be handy, but if there's not something
   > comparable in the SQL standard, I'd rather see several other features added long
   > before this one. Just my two cents.
   > 
   
   It's not in MSSQL Server at least through version 7 (just checked). I 
   don't think it has been added in MSSQL 2000 either, but I can't really 
   check that at the moment.
   
   MS Access syntax is like this:
   
   TRANSFORM Max(Table1.value) AS MaxOfvalue
   SELECT Table1.name
   FROM Table1
   GROUP BY Table1.name
   PIVOT Table1.cat;
   
   but it *only* seems to allow TRANSFORM on an aggregate.
   
   Syntax
   TRANSFORM aggfunction
   selectstatement
   PIVOT pivotfield [IN (value1[, value2[, ...]])]
   
   
   Joe