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
        }
    }
}

Quick script for PivotTable-like functionality in PowerShell. I find myself using this a lot.

# Rotates a vertical set similar to an Excel PivotTable
#
# Given $data in the format:
#
# Category     Activity     Duration
# ------------ ------------ --------
# Management   Email               1
# Management   Slides              4
# Project A    Email               2
# Project A    Research            1
# Project B    Research            3
#
# with $keep = "Category", $rotate = "Activity", $value = "Duration"
#
# Return
#
# Category   Email Slides Research
# ---------- ----- ------ --------
# Management     1      4
# Project A      2               1
# Project B                      3

$rotate = "Activity"
$keep = "Category"
$value = "Duration"

$pivots = $data | select -unique $rotate | foreach { $_.Activity}

$data |
	group $keep |
	foreach {
		$group = $_.Group
		$row = new-object psobject
		$row | add-member NoteProperty $keep $_.Name
		foreach ($pivot in $pivots) { $row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum }
		$row
	}