Обсуждение: Cross-Tab queries in postgres?
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. 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). For those who don't understand what I mean by cross-tab, it's the conversion of: A | b | 1 A | c | 2 B | a | 3 B | b | 4 C | c | 5 into: Column headings: a, b, c A | | 1 | 2 B | 3 | 4 | C | | | 5 -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
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. > Funny you should ask about this today. See my post to patches from yesterday: http://archives.postgresql.org/pgsql-patches/2002-07/msg00247.php specifically: crosstabN(text sql) - returns a set of row_name plus N category value columns - crosstab2(), crosstab3(), and crosstab4() are defined for you, but you can create additional crosstab functions per directions in the README. crosstabN example usage test=# select * from ct where rowclass = 'group1' and (attribute = 'att2' or attribute = 'att3'); id | rowclass | rowid | attribute | value ----+----------+-------+-----------+------- 2 | group1 | test1 | att2 | val2 3 | group1 | test1 | att3 | val3 6 | group1 | test2 | att2 | val6 7 | group1 | test2 | att3 | val7 (4 rows) select * from crosstab3( 'select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;'); row_name | category_1 | category_2 | category_3 ----------+------------+------------+------------ test1 | val2 | val3 | test2 | val6 | val7 | (2 rows) and a follow-up at: http://archives.postgresql.org/pgsql-patches/2002-07/msg00250.php If you want to try it get an update from cvs and apply the three patches (well, one is just a doc patch) from the *second* post. This is not exactly what you have described, but pretty close. Take a look at the README. I think it currently is not as flexible as your example would need, but could be reasonably easily modified. > 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.
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)
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
Hello, Is there a way in PostgreSQL to create unique table names? I need them for multi-frame web-browser requests. Each frame calls a PHP script; the first frame creates a table based on an (expensive) query, the other frames use the table in parallel, and when finished, a final frame deletes the table. Each frame executes with its own database connection, so temporary tables cannot be used (or can they in some way?). I can generate names at the client side, but is there a simple way to do so within the database, e.g. by writing a pl/pgsql of C function? Thanks in advance, Jan Hartmann