[jira] [Commented] (DRILL-4223) PIVOT and UNPIVOT to rotate table valued expressions

[Ted Dunning (JIRA)]

[ 
https://issues.apache.org/jira/browse/DRILL-4223?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16388637#comment-16388637
 ] 

Ted Dunning commented on DRILL-4223:


 

This is actually related to list_aggregate and some kind of inverse to 
flatten/unnest. My guess is that if we had a JSON constructor, this would be 
just about as good. The idea would be that columns could be specified to 
determine the key and value in an object. Aggregation would be the final step 
to get what John wants. Aggregation over structures is an open question since 
you don't necessarily know the keys in a structure. It would be nice to be able 
to apply an aggregation function to all members of the structure without 
knowing which members exist.

 

 

> PIVOT and UNPIVOT to rotate table valued expressions
> 
>
> Key: DRILL-4223
> URL: https://issues.apache.org/jira/browse/DRILL-4223
> Project: Apache Drill
>  Issue Type: New Feature
>  Components: Execution - Codegen, SQL Parser
>Reporter: Ashwin Aravind
>Priority: Major
>
> Capability to PIVOT and UNPIVOT table values expressions which are results of 
> a SELECT query



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Commented] (DRILL-4223) PIVOT and UNPIVOT to rotate table valued expressions

[John Omernik (JIRA)]

[ 
https://issues.apache.org/jira/browse/DRILL-4223?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16388521#comment-16388521
 ] 

John Omernik commented on DRILL-4223:
-

The ability to PIVOT data would be extremely helpful, especially for data 
visualization software that looks for data in certain format. A use case that 
came up for me would be this example:

 

source dt value
X 2018-03-06 11:00 0.31
X 2018-03-06 12:00 0.94
X 2018-03-06 13:00 0.89
X 2018-03-06 14:00 0.01
X 2018-03-06 15:00 0.43
Y 2018-03-06 11:00 1.43
Y 2018-03-06 12:00 0.50
Y 2018-03-06 13:00 0.10
Y 2018-03-06 14:00 0.42
Y 2018-03-06 15:00 0.41
Z 2018-03-06 11:00 5.34
Z 2018-03-06 12:00 4.32
Z 2018-03-06 13:00 4.20
Z 2018-03-06 14:00 0.89
Z 2018-03-06 15:00 0.01

I'd like to graph it as three lines (X, Y and Z) over time, so the graph tool I 
am using asks for it this format:

 

dt X Y Z

2018-03-06 11:00 0.31 1.43 5.34
2018-03-06 12:00 0.94 0.50 4.32
2018-03-06 13:00 0.89 0.10 4.20
2018-03-06 14:00 0.01 0.42 0.89
2018-03-06 15:00 0.43 0.41 0.01


It appears it's a SQL Server thing, but could it also work in Drill?


https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server

> PIVOT and UNPIVOT to rotate table valued expressions
> 
>
> Key: DRILL-4223
> URL: https://issues.apache.org/jira/browse/DRILL-4223
> Project: Apache Drill
>  Issue Type: New Feature
>  Components: Execution - Codegen, SQL Parser
>Reporter: Ashwin Aravind
>Priority: Major
>
> Capability to PIVOT and UNPIVOT table values expressions which are results of 
> a SELECT query



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)