How to normalize incoming events in a Stream Analytics job

If you want to normalize the incoming events in a Stream Analytics job and transform records with the following format:

to the following format:

you can use the GetRecordProperties function and CROSS APPLY operator as shown by the following query:

 SELECT e.Time, e.Id, record.PropertyName as Type, record.PropertyValue AS Value
FROM Events e
CROSS APPLY GetRecordProperties (e) AS record
WHERE record.PropertyName = 'p3' OR
      record.PropertyName = 'p2' OR
      record.PropertyName = 'p3'

You can use the following input file to test the Stream Analytics query on the Azure Management portal.

 [
  {
    "Time": "2015-12-16T10:00:01.0000000Z",
    "Id": "01",
    "P1": 15,
    "P2": 30,
    "P3": 10,
  },
  {
    "Time": "2015-12-16T10:00:01.0000000Z",
    "Id": "02",
    "P1": 25,
    "P2": 40,
    "P3": 20,
  },
  {
    "Time": "2015-12-16T10:00:01.0000000Z",
    "Id": "03",
    "P1": 45,
    "P2": 18,
    "P3": 5,
  },
  {
    "Time": "2015-12-16T10:00:01.0000000Z",
    "Id": "04",
    "P1": 20,
    "P2": 23,
    "P3": 56,
  }
]

Running the query against the test file returns the following dataset: