USQL support pivot - looking for an example


  • We have a requirement to do the dynamic pivot of the data using USQL. We have input data as below

    id	   timestamp	 key	value
    2	  1520300241230	 key1	100
    2	  1520300241230	 key2	101
    2	  1520300241230	 key4	103
    2	  1530300241230	 key1	200
    2	  1530300241230	 key2	201
    2	  1530300241230	 key3	202

    That needs to be converted as below

    id	timestamp	key1	        key2	        key3	        key4
    2	1520300241230	100		101		#		103
    2	1530300241230	200		201		202		#

    Does USQL support pivot to do the same ? 

    We should be able to group (key1, key2...keyN) based on input!

    Sunday, December 2, 2018 8:30 PM

All replies

  • Monday, December 3, 2018 6:35 PM
  • Hi  Jason_J,

    Thanks for the link. The link does not solve the problem we face. We were close to a solution using PIVOT in USQL and passing required columns at the compile time of USQL using Azure data factory. However the IN clause fails in USQL if we pass multiple values comma separated (@ids) in search string, example below. If we pass only "key1"  or some other value alone then the IN clause matches. For multiple inputs we need to create multiple scalar variables ( like @id1="key1", @id2="key2"...etc) , which is not possible in our case.

    DECLARE EXTERNAL @ids string = "key1,key2,key3,key4"; //"external declaration"

    @result = SELECT * FROM (SELECT id, timestamp, key, value FROM @data ) AS D PIVOT(SUM(value) FOR key IN(@ids AS heading)) AS P

    Is there any way to pass multiple values as single scalar variable in USQL for the IN clause find the result?

    Sunday, December 9, 2018 7:24 PM
  • Hi All, 

    We were able to make a solution, by creating a dynamic USQL out of USQL, then invoking the new USQL.




    Tuesday, December 11, 2018 12:06 PM