Arjun's approach works even if the timestamps are not unique. Especially if
you use avg instead of max.

On Mar 6, 2018 8:47 AM, "Arjun kr" <arjun...@outlook.com> wrote:

> If each timestamp has only one set of values for (x,y,z) , you can try
> something like below.
>
> select dt ,
>  max(case when source='X' THEN `value` else 0.0 end) as X,
>  max(case when source='Y' THEN `value` else 0.0 end) as Y,
>  max(case when source='Z' THEN `value` else 0.0 end) as Z
> from
>  <table>
> group by dt;
>
> Thanks,
>
> Arjun
>
>
>
> ________________________________
> From: Andries Engelbrecht <aengelbre...@mapr.com>
> Sent: Tuesday, March 6, 2018 9:11 PM
> To: user@drill.apache.org
> Subject: Re: Way to "pivot"
>
> If the X, Y and Z is unique for each timestamp you can perhaps use group
> by (dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth
> looking into, but is going to be expensive to execute. Just an idea, but
> have not tested it.
>
> --Andries
>
>
> ´╗┐On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:
>
>     I am not sure if this is the right thing for what I am trying to do,
> but I
>     have data in this formate
>
>
>     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
>
>
>     So I think that would be a PIVOT like function right (which I don't
> think
>     Drill has) Is there a way to "fake" this in Drill using some other
> built in
>     functions?
>
>     Thanks!
>
>     John
>
>
>

Reply via email to