locked
Is there a way to pivot a normalized table in ASA? RRS feed

  • Question

  • Hey all, just needing some help here to learn how a normalized table can be pivoted in ASA. Any examples using UDF or ASA Query will be much appreciated. Thank-you!
    Monday, December 17, 2018 7:49 PM

All replies

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

    For more information, refer to Azure Stream Analytics JavaScript user-defined functions.

    Also, provide a feedback on UserVoice forum and upvote for this feature.

    Tuesday, December 18, 2018 7:31 AM
  • Thanks, Ashok. I am aware that UDF's can be used for this purpose, is there an example that can be shared?
    • Edited by Harry Grewall Tuesday, December 18, 2018 1:12 PM text
    Tuesday, December 18, 2018 1:11 PM
  • Hi,

    Here's an example of the SQL part needed to call a UDF to pivot data with the same DateTime.

    For the UDF, what it depends how you want to Pivot it. You will need to write PivotRecords function that receives array of JSON objects and returns single JSON object in the desired output format. If you have a sample input and output we can help you.

    SELECT udf.PivotRecords(Collect())

    GROUP BY System.Timestamp

     


    Wednesday, December 26, 2018 6:07 PM