Words: 1151
Time to read: ~ 6 minutes
Table of Contents
Apologies
I’m going to start this post off with an apology.
As Kevin Feasel ( Blog | Twitter ) mentioned about my last post Attempting SUM() OVER () in PowerShell:
It’d be a lot easier, though, with a properly normalized data model which includes date, attempt number, and push-ups in that attempt. Pivot those results at the end if you want this sort of report, but SQL is designed to work best with tables in first normal form or higher.
Kevin Feasel
I can’t very well give out to people for not doing the right thing first time, even if it’s more difficult, if I don’t do the right thing myself!
As Kevin mentioned, once the data was in a proper format, a format designed for SQL, the calculations were trivial.
However, outputting the results in the same way in PowerShell required a way to pivot results in PowerShell. Thanks to some heavy lifting from Joel Sallow ( Blog | Twitter ), I now know how to pivot in PowerShell!
Here’s hoping that this post will help explain it for you also.
Exploring our Data
SQL
First off, let’s check the current state of our table in SQL.
SELECT POP.pushup_date,
POP.attempt_number,
POP.pushup_count,
SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total_per_date,
SUM(POP.pushup_count) OVER () AS grand_total
FROM dbo.PushupsOctoberProper AS POP;
Pivoting
I want to get all possible 8 attempts horizontal like the last post. I find this fairly easy when I have the documentation for PIVOTs open in another tab.
/* Can we pivot these? */
SELECT PVT_01.pushup_date,
[1] AS attempt_1,
[2] AS attempt_2,
[3] AS attempt_3,
[4] AS attempt_4,
[5] AS attempt_5,
[6] AS attempt_6,
[7] AS attempt_7,
[8] AS attempt_8,
PVT_01.total,
PVT_01.total_so_far
FROM
(
SELECT POP.pushup_date,
POP.attempt_number,
POP.pushup_count,
SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total,
SUM(POP.pushup_count) OVER () AS total_so_far
FROM dbo.PushupsOctoberProper AS POP
) AS SRC
PIVOT
(
MAX(pushup_count) FOR attempt_number IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS PVT_01
ORDER BY PVT_01.pushup_date;
Simple, right? Once we have the data in the expected format then the above steps are the only steps necessary to calculate and show the data in the way that we want.
However, it becomes a bit more complicated in PowerShell.
PowerShell
Let’s grab the data from our SQL instance and take a look at it.
<# Populate our variable from the database #>
$invQueryParams = @{
SqlInstance = $sqlInstance
Database = 'LocalTesting'
Query = 'SELECT * FROM dbo.PushupsOctoberProper;'
}
$data = Invoke-DbaQuery @invQueryParams
<# Show our data #>
$data | Format-Table -Autosize
Grouping our Data
We have our data fetched, now we need to group it by the different dates. If only PowerShell had a way to group objects…what? Group-Object? oh!
<# Grouping our data #>
$dataGroups = $data | Group-Object -Property pushup_date
$dataGroups
Now that we have our data grouped by the different dates, we can loop through each date and pivot the data out horizontally.
Manual Pivot
The first way that came to mind was to manually list out all columns. I know that the maximum attempt_count that I have is 8 so let’s manually create 8 attempt columns.
<# Let's pivot this manually because it's the first way that came to mind #>
$ManualpivotedData = foreach ($dg in $dataGroups) {
[PSCustomObject]@{
pushup_date = ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString()
attempt01 = ($dg.Group | Where-Object attempt_number -eq 1).pushup_count
attempt02 = ($dg.Group | Where-Object attempt_number -eq 2).pushup_count
attempt03 = ($dg.Group | Where-Object attempt_number -eq 3).pushup_count
attempt04 = ($dg.Group | Where-Object attempt_number -eq 4).pushup_count
attempt05 = ($dg.Group | Where-Object attempt_number -eq 5).pushup_count
attempt06 = ($dg.Group | Where-Object attempt_number -eq 6).pushup_count
attempt07 = ($dg.Group | Where-Object attempt_number -eq 7).pushup_count
attempt08 = ($dg.Group | Where-Object attempt_number -eq 8).pushup_count
total = ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum
total_so_far = ($data | Measure-Object -Property pushup_count -Sum).Sum
}
}
<# Let's make this pretty #>
$ManualpivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
In case you’re wondering what @{ Expression = 'attempt*' ; Width = 10 } does, I use it to narrow the width of the columns named like attempt since they’re integers. Since they don’t need as much space, I can narrow them down and then Format-Table won’t cut-off my later columns!
Dynamic Pivot
I’m not against the manual way. I just find it too bulky and repetitve. It works! Please don’t get me wrong on that accout but as I recently heard someone say: “It works, now clean it up“
Our main problem is the attempt columns and our manually typing them out. They seem like a perfect candidate for a ForEach loop. But, when we try to slot that in….
foreach ($dg in $dataGroups) {
$props = @(
@{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
foreach ($num in 1..8) {
@{
Name = "attempt_$num"
Expression = { $dg.Group |
Where-Object attempt_number -eq $num |
Select-Object -ExpandProperty pushup_count }
}
}
@{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
@{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
)
$dg | Select-Object $props
}
Yeah, that’s a “no” from PowerShell. Why is this?
Why this is
Let’s investigate that $props variable. We’re creating a hashtable where the Key is our name and the Value is the expression we want. So let’s get the values.
$props | Format-List
Do you see the way that each of the Expression keys have a value with the $num variable?
If you check $num now, you’ll see that it’s set to 8. It looks like we have found our problem, the $props variable isn’t keeping the value of $num when we define it!
Since only one date has a value for attempt 8, we should see some values there.
$pivotedData = foreach ($dg in $dataGroups) {
$props = @(
@{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
foreach ($num in 1..8) {
@{
Name = "attempt_$num"
Expression = { $dg.Group |
Where-Object attempt_number -eq $num |
Select-Object -ExpandProperty pushup_count }
}
}
@{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
@{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
)
$dg | Select-Object $props
}
<# Let's check the 14th #>
$pivotedData | Where-Object pushup_date -eq '14/10/2020'
Yeah…that’s not correct. I did 30 on the first attempt. Believe me, I remember the pain. Looks like it’s putting the value for attempt 8 into each of the attempts.
Not cool…
Closures
If only there was a way to keep the value of $num when we defined the $props variable. Well, thanks to Joel and his post ScriptBlocks and GetNewClosure(), I now know that there is!
$pivotedData = foreach ($dg in $dataGroups) {
$props = @(
@{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
foreach ($num in 1..8) {
@{
Name = "attempt_$num"
Expression = { $dg.Group |
Where-Object attempt_number -eq $num |
Select-Object -ExpandProperty pushup_count }.GetNewClosure()
}
}
@{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
@{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
)
$dg | Select-Object $props
}
<# Let's make this pretty #>
$pivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
Summary
There’s nothing wrong with making mistakes; as long as you learn from them.
Thanks to Kevin for reminding me how things should be stored, and thanks to Joel for this (vast) knowledge sharing, I’ve been able to learn how to dynamically pivot in PowerShell from my mistakes.
Review your mistakes, you never know what you may learn.
Thanks for this post! Pivot tables can be a bit complicated and you do a good job explaining them and how to do the equivalent in PS.
One suggestion to improve your solution: the total_so_far is and will be the same for all rows since it’s a sum of $data, not $dg. As is the program recalculates it for each row when really it could just be calculated once and put in the results.