PoshCode Archive  Artifact [2814df2611]

Artifact 2814df26110a97bb370b3fe06ff3a58e0a810cc18c0b8b704b26849524bb6d7d:

  • File Pivot-Object.ps1 — part of check-in [dbb811d9f0] at 2018-06-10 13:25:31 on branch trunk — Takes a series of objects (like the converted input from csv) where there are a series of objects (rows) that actually define the same object, and there is (at least) one property which is a unique identifier (and appears on each object), and there are two properties which are a name and value pair defining additional properties ... and outputs new objects which merge the objects and add the name-value pairs as new properties. (user: Joel Bennett size: 5061)

# encoding: ascii
# api: powershell
# title: Pivot-Object
# description: Takes a series of objects (like the converted input from csv) where there are a series of objects (rows) that actually define the same object, and there is (at least) one property which is a unique identifier (and appears on each object), and there are two properties which are a name and value pair defining additional properties ... and outputs new objects which merge the objects and add the name-value pairs as new properties.
# version: 0.1
# type: script
# author: Joel Bennett
# license: CC0
# function: Pivot-Object
# x-poshcode-id: 3434
# x-archived: 2015-11-12T23:51:22
# x-published: 2012-05-28T21:28:00
#
#
#.Synopsis
#   Pivot multiple objects which are normalized with properties that are name, value pairs.
#.Description
#  Takes a series of objects where there are multiple rows to represent each object, with a pair of columns with the name and value of an additional property, and outputs new objects which have those new properties on them.
#.Example
#  Import-CSV data.csv | Pivot-Objects SamAccountName Attribute Value
#
#  Imports csv data containing multiple rows per-record such that a pair of columns named "Attribute" and "Value" are actually different in each row, and contain a name and value pair for attributes you want to add to the output objects.
#.Example
# @"
#   ID,    Attribute,     Value,       SamAccountName
#     ,    HoursPerWeek,  40,          J8329029
#     ,    LastDay,       20 Feb 2010, J8329029
#   12276, Job,           Sw Eng,      J8329029
#   2479,  HoursPerWeek,  35,          X5969731
#   2479,  LastDay,       20 Feb 2009, X5969731
#   2479,  Job,           Sr. SW Eng,  X5969731
#   2479,  Rate,          40,          X5969731
#   2479,  Bonus,         10000,       X5969731
# "@.Split("`n") | ConvertFrom-Csv | Pivot-Object -Group SamAccountName -Name Attribute -Value Value -Jagged
#   
#  This provides a full example, with data, and demonstrates how -Jagged works:
#  * Without -Jagged, you get two objects which each have the same properties. 
#  * With Jagged, only one one of the object output will have the Rate and Bonus properties, because there's values for the J8329029 user.
#.Notes
#  Author: Joel Bennett
#  function Pivot-Object {
[CmdletBinding()]
param(
   # The name of the property to merge on. Items with the same value in this column will be combined.
   [Parameter(Mandatory=$true)]
   [Alias("GroupBy")]
   [string]$GroupOnColumn,
   # The name of the property that has the names of the new properties to create.
   [Parameter(Mandatory=$true)]
   [string]$NameColumn,
   # The name of the property that has the values of the new properties to create.
   [string]$ValueColumn,
   [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]
   # The input objects.  These can be passed on the pipeline as well (or instead).
   [object[]]$InputObject=@(),
   # If set, the objects that return only have the properties which were defined for them.
   # Otherwise, all properties that are defined for any object are assigned (even if that means they get null values) on all objects that are output.
   # See the examples for more information
   [switch]$Jagged
)
begin {
   [string[]]$Extra = @()
   [string[]]$NamedColumns = @()
   $CollectedInput = New-Object 'System.Collections.Generic.List[PSObject]'
}
process{
   $Extra += $InputObject | Get-Member -Type Properties | 
                            Select-Object -Expand Name -Unique | 
                            Where-Object {($Extra + @($GroupOnColumn,$NameColumn,$ValueColumn)) -NotContains $_}

   foreach($Incoming in $InputObject) {
      $NamedColumns += $Incoming.$NameColumn
      $null = $CollectedInput.Add($Incoming)
   }
}
end {
   $Columns = $NamedColumns | Select-Object -Unique | Where-Object { $_ }
   $Extra = $Extra | Select-Object -Unique | Where-Object { $_ -and ($Columns -NotContains $_) }
   
   ForEach($item in $CollectedInput | Group-Object $GroupOnColumn) {
      $thing = New-Object PSObject |
               Add-Member -Type NoteProperty -Name $GroupOnColumn -Value $Item.Name -passthru
 
      Write-Verbose "Group $($Item.Name)"
      foreach($c in $Extra) {
         $Value = $item.Group | Select-Object -Expand $c -Unique | Where-Object { $_ } | Select-Object -First 1
         Write-Verbose "Value $($Value)"
         if($Value) {
            Add-Member -input $thing -MemberType NoteProperty -Name $c -Value $Value
         }
      }
 
      foreach($i in $item.Group) {
         Add-Member -Input $thing -Type NoteProperty -Name $i.$NameColumn -Value $i.$ValueColumn
      }
      
      if($Jagged) {
         Write-Output $thing
      } else {
         # Note: I add the GroupOnColumn and Extra columns when -Jagged isn't specified ... I assume that's what you want.
         Write-Output $thing | select ([string[]]@(@($GroupOnColumn) + $columns + $extra))
      }
   }
}
#}