Skip to main content

More PivotTables in PowerShell

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.

[sourcecode language="powershell"]

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

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

} [/sourcecode]