Andy Oakley

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
	}