-1

I am working in streaming of data by using Azure Stream analytics job which will use T SQL Query. I Have the following Requirement:

I can do it by using case and sum statements, but I have lot of countries and issues (L3), so is there any pivot option I can use?

gung - Reinstate Monica
  • 11,583
  • 7
  • 60
  • 79
Manoj
  • 61
  • 1
  • 2
  • 8

2 Answers2

0

You can query using pivot as below:

Select * from (
    Select Co_nm, TimePeriod, CONCAT(L3, ISO_Cntry_Nm) as L3Country, [No of issues] 
        from #yourtable
        ) a
pivot (max([No of issues]) for L3Country in ([Issue 1Great Britain], [Issue 1India],[Issue 1America],[Issue 2Great Britain], [Issue 2India],[Issue 2America] ) ) p
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • Thanks for that, but i have No.of combinations of L3 and Countries so isthere any approach like Shape in R program , i can get with formula? – Manoj Jul 12 '17 at 06:28
0

The most flexible way is using collect() to get an array of events, and pass it to a JavaScript UDF to do the pivot.

WITH EventCollection AS 
(
  SELECT collect() AS allEvents
  FROM input TIMESTAMP BY timeperiod
  GROUP BY System.Timestamp
),
PivotRecord AS
(
  SELECT udf.getPivotRecord(allEvents) AS record
  FROM EventCollection 
)
SELECT record.*
INTO output
FROM PivotRecord

For example JavaScript UDF that handles array, see this blog post Using Azure Stream Analytics JavaScript UDF to lookup values in JSON array.

MinHe-MSFT
  • 279
  • 1
  • 2
  • Thanks for the solution but , i have zero knowledge on JAVA , so it would be better if i use T sql , im looking for SImilar function like CAST in R program but in TSQL – Manoj Jul 12 '17 at 06:30