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 > > >
