You are viewing a plain text version of this content. The canonical link for it is here.
Posted to codereview@trafodion.apache.org by GitBox <gi...@apache.org> on 2019/03/14 17:52:39 UTC
[GitHub] [trafodion] DaveBirdsall commented on a change in pull request
#1814: [TRAFODION-3286] Add the PIVOT Function in the Trafodion SQL
Reference Manual
DaveBirdsall commented on a change in pull request #1814: [TRAFODION-3286] Add the PIVOT Function in the Trafodion SQL Reference Manual
URL: https://github.com/apache/trafodion/pull/1814#discussion_r265694020
##########
File path: docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
##########
@@ -7096,6 +7096,98 @@ PI()
PI()
```
+<<<
+[[pivot_function]]
Review comment:
I was new to the PIVOT function, actually, so I did some experiments to figure out how it works. It's a cool function!
It is an aggregate function (like SUM or COUNT); it behaves by concatenating the string representation of the values using the delimiter to separate the values. I think your discussion would be more clear if you mention that it is an aggregate function, and if you include the original table that your examples are drawn against. You might, for example, show the DDL of table VENDOR, and also show the initial set of rows in it.
Since it is an aggregate function, I found I could do interesting things with it. For example, suppose I have a table VENDOR1, with primary key (vendor_id, e_mail_id), and column vendor_email. Suppose my initial rows are:
>>select * From vendor1;
VENDOR_ID EMAIL_ID VENDOR_EMAIL
----------- ----------- ------------------------------
111 1 tom@hotmail.com
232 1 jerry@hotmail.com
367 1 aven@hotmail.com
367 2 aven@gmail.com
--- 4 row(s) selected.
>>
That is, this table has a row in it for each vendor e-mail ID. (Some vendors, like # 367 have more than one.)
Then I could do a query that groups by vendor_id, gluing together their e-mail IDs:
>>select vendor_id,pivot(vendor_email) from vendor1
+>group by vendor_id;
VENDOR_ID (EXPR)
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
111 tom@hotmail.com
232 jerry@hotmail.com
367 aven@hotmail.com,aven@gmail.com
--- 3 row(s) selected.
>>
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services