Pivot table without aggregation

Category: azure stream analytics

Question

JdBruin on Thu, 02 Mar 2017 05:12:55


Hi.

I have the following json input from EventHub:

{"MeasurementReading":
[{"TimeStamp":"2017-02-23T07:08:49","Tag":"Tag1","Value":"1","Good":"true","Questionable":"false","Substituted":"false"},
{"TimeStamp":"2017-02-23T07:08:49","Tag":"Tag2","UnitsAbbreviation":"","Value":"2","Good":"true","Questionable":"false","Substituted":"false"},
{"TimeStamp":"2017-02-23T07:08:49","Tag":"Tag3","UnitsAbbreviation":"","Value":"3","Good":"true","Questionable":"false","Substituted":"false"},
{"TimeStamp":"2017-02-23T07:08:49","Tag":"Tag4","UnitsAbbreviation":"","Value":"4","Good":"true","Questionable":"false","Substituted":"false"},
{"TimeStamp":"2017-02-23T07:08:49","Tag":"Tag5","UnitsAbbreviation":"","Value":"5","Good":"true","Questionable":"false","Substituted":"false"},
{"TimeStamp":"2017-02-23T07:08:49","Tag":"Tag6","UnitsAbbreviation":"","Value":"6","Good":"true","Questionable":"false","Substituted":"false"}]}

I would like write a query within the Streaming Analytic  that would will pivot the table that would produce the following table:

TimeStamp,                     Tag1,Tag2,Tag3,Tag4,Tag5,Tag6

"2017-02-23T07:08:49",1       ,2,     3,      4,    5,       6

I have tried to solves this for a day no with no luck, I would appreciated any help.

Thanks


Replies

JS Azure on Wed, 08 Mar 2017 00:39:06


Hi,

This feature is not yet available as part of the standard ASA Query Language, but it is possible to create a User Defined Functions to do this.

More information is available here: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-javascript-user-defined-functions

Don't hesitate to upvote this feature request here: https://feedback.azure.com/forums/270577-stream-analytics/suggestions/14787960-need-pivot-function-for-azure-stream-analytics

Thanks,

JS - Azure Stream Analytics Team