azure stream analytics combine data with same time

Question

I am working with azure stream analytics query language and i have some problems with data preparation.this is my current output

but i want to combine data with same time for example in first line {"46027020", "#A83","2017-05-18T08:47:26.5620000Z"} with header "IGEF_NR", "Decklack" and "time" second line: {"46027070", "#475","2017-05-18T08:49:20.1750000Z"}

Answer 1

You will need split the filter sub-query to "IGEF_NR" and "DECKLACK", then join the two. Something like below:

filter_IGEF AS
(
  SELECT sub_id, value,time
  FROM stringfilter
  WHERE sub_id = 'IGEF_Nr'
 ),

 filter_Decklack AS
(
  SELECT sub_id, value,time
  FROM stringfilter
  WHERE sub_id = 'Decklack'
 ),

 joined_value AS
 (
   SELECT
     i.value AS IGEF_Nr,
     d.value AS Decklack,
     i.time as time
   FROM filter_IGEF i
   JOIN filter_Decklack d
   ON i.time = d.time
   AND DATEDIFF(second, i, d) BETWEEN 0 AND 5
 )

 SELECT IGEF_Nr, Decklack, time
 INTO csv
 FROM joined_value