trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From GitBox <...@apache.org>
Subject [GitHub] [trafodion] DaveBirdsall commented on a change in pull request #1814: [TRAFODION-3286] Add the PIVOT Function in the Trafodion SQL Reference Manual
Date Thu, 14 Mar 2019 17:52:39 GMT
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

Mime
View raw message