Pivot Example

gistfile1.ps1

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