Andy Oakley

Similar to the Simple PivotTables in Excel script I wrote a while back, today I needed to do a different type of pivot by hierarchy. I had a flat table of rows which I wanted to group by several columns recursively.

# Given data such as
#
# A       B           C
# ------- ----------- ----
# red     lisbon      cat
# blue    venice      dog
# green   paris       cat
# blue    london      dog
# green   lisbon      fish
# green   paris       cat
#
# Output the following
#
#  cat
#     red
#         lisbon           1
#     green
#         paris            2
#  dog
#     blue
#         venice           1
#         london           1
#  fish
#     green
#         lisbon           1
#
# $list | output-hierarchy "C","A","B"

function output-hierarchy
{
    param($hierarchy,$depth=0)
    if ($depth -gt 5) { return }

    $groups = $input | group $hierarchy[0]
    foreach ($group in $groups)
    {
        if ($hierarchy.length -gt 1)
        {
            # Emit title at this level
            "`t"*$depth + $group.name

            $group.group | output-hierarchy $hierarchy[1..($hierarchy.length-1)] ($depth+1)
        }
        else
        {
            # Figure out how many spaces to right align count
            $spacer=(60-$group.name.length-$depth-$group.group.count.tostring().length)

            # End of the road, count the remaining items that fall into this category
            "`t"*$depth + $group.name + " "*$spacer + $group.group.count
        }
    }
}