Monday, April 11, 2016

Transforming Rows into Columns in U-SQL

Pivot operator in SQL helps us to turn the unique values of a specified column from a single/multiple rows into multiple column values in the output. Additionally we can also perform aggregations, wherever required, for column values that are necessary in the final output.

If you want similar functionality in your U-SQL query, you have to use the CROSS APPLY operator of U-SQL to essentially do the similar job.

@output = 
    SELECT 
        Region, 
        Urls
    FROM @searchlog;

@output =
    SELECT 
        Region, 
        SqlArray.Create(Urls.Split(';')) AS UrlTokens
    FROM @output;

@output = 
    SELECT 
        Region, 
        Token AS Url
    FROM  @output
    CROSS APPLY EXPLODE (UrlTokens) AS r(Token);

Cross Apply Output

0
1
"en-us"
"www.nachos.com"
"en-us"
"www.wikipedia.com"
"en-gb"
"skiresorts.com"
"en-gb"
"ski-europe.com"
"en-gb"
"www.travelersdigest.com/ski_resorts.htm"
"en-gb"
"mayoclinic.com/health"
"en-gb"
"webmd.com/a-to-z-guides"
"en-gb"
"mybrokenleg.com"
"en-gb"
"wikipedia.com/Bone_fracture"
"en-ca"
"southparkstudios.com"
"en-ca"
"wikipedia.org/wiki/Sout_Park"
"en-ca"
"imdb.com/title/tt0121955"
"en-ca"
"simon.com/mall"
"en-us"
"cosmos.com"
"en-us"
"wikipedia.org/wiki/Cosmos:_A_Personal_Voyage"
"en-us"
"hulu.com/cosmos"
"en-fr"
"microsoft.com"
"en-fr"
"wikipedia.org/wiki/Microsoft"
"en-fr"
"xbox.com"
"en-us"
"www.amazon.com"
"en-us"
"reviews.cnet.com/wireless-headphones"
"en-us"
"store.apple.com"
"en-us"
"dominos.com"
"en-us"
"wikipedia.org/wiki/Domino's_Pizza"
"en-us"
"facebook.com/dominos"

Converting columns into Rows


Similarly if we want to convert a set of columns into rows or revert back to the previous state (refer the above situation), we have to use ARRAY_AGG operator. In the example below you will see rowset taken apart by CROSS APPLY and then reconstructed via the ARRAY_AGG operator.

@a = SELECT Region, Urls FROM @searchlog;

@b =
    SELECT 
        Region, 
        SqlArray.Create(Urls.Split(';')) AS UrlTokens
    FROM @a;
@c = 
    SELECT 
        Region, 
        Token AS Url
    FROM  @b
    CROSS APPLY EXPLODE (UrlTokens) AS r(Token);
@d =
    SELECT Region,
           string.Join(";", ARRAY_AGG(Url).ToArray()) AS Urls
    FROM @a
    GROUP BY Region;