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