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 [email protected]
232 1 [email protected]
367 1 [email protected]
367 2 [email protected]
--- 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 [email protected]
232 [email protected]
367 [email protected],[email protected]
--- 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:
[email protected]
With regards,
Apache Git Services
