Thursday, March 31, 2016

Pivot in U-SQL

In my case I needed to pivot a table with date by a week day. Meaning, having table like:

Transno Date
1223 11/01/2016
2795 12/01/2016
To get result as:
Transno 1 2 3 4 5 6 7
1223 0 1 0 0 0 0 0
2795 0 0 1 0 0 0 0
Where corresponding week day number column gets value 1, and others - 0... Outputting result to a .csv, of course.

First I select transno, day of week as a number, and some constant for future pivotting.
@res1 =
    SELECT Transno, Convert.ToInt32(Date.DayOfWeek) AS wd, 1 AS a1
    FROM dbo.LocalTransno;

Then use MAP_AGG, which is a base for pivot:
@res2 =
    SELECT Transno,           
           1 AS k1,
           MAP_AGG(wd, (int?) a1) AS mapwd
    FROM @res1
    GROUP BY Transno;

Here I create a dummy table with 1 row, just to select values from 1 to 7 (my weekdays numbers) - as a key values for pivot.
@one = SELECT * FROM (VALUES(1)) AS T(a);
@keys = 
    SELECT 
        1 AS k1, 
        Inmeta.USQLScripts.Helper.InitList(1, 7) AS dkeys
    FROM @one;

Where InitList s a code-behind function returning an object of SqlArray<int>:

  public partial class Helper  {
       public static SqlArray<int> InitList(int lower, int upper) {
            var values = new List<int>();
            for (int i = lower; i <= upper; i++)  {
                values.Add(i);
            }
            var res = new SqlArray<int>(values) ;
            return res;
        }
  }

And finally, pivotting and unrolling to comma-separated format at once:
@res =
    SELECT 
    a.Transno.ToString() + ","  
    String.Join(",", b.dkeys.ToList().Select(k => a.mapwd.ContainsKey(k) ? 1 : 0)) 
    AS x
    FROM @res2 AS a
    JOIN @keys AS b ON a.k1 == b.k1;
Note the trick of dummy join, to connect weekdays keys table with my data :)

And finally output - removing the quotes from string output gives a clean csv :
OUTPUT @res
TO @out
USING Outputters.Csv(quoting : false);

It's a bit of running around, but that's the only way I managed to make it work.

1 comment: