PoshCode Archive  Check-in [6df4f1cac8]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:6df4f1cac8b5da14fb63208ad2e918c714e3dd1176d2017a800c7fc9910c3c31
User & Date: Joel Bennett 2018-06-10 14:25:57
Context
2018-06-10
14:25
Demo of different ways to split a text into lines. Sorry I have not yet another nice way to post code in my blog. http://pauerschell.blogspot.com/. Understanding TextToLines splitting is basic to ISE-Extensions. check-in: 274fa9ed3c user: Bernd Kriszio tags: trunk
14:25
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. check-in: 6df4f1cac8 user: Joel Bennett tags: trunk
14:25
It would really be sweet if I could get-netstat -sate CLOSE_WAIT check-in: f2695362b7 user: glnsize tags: trunk
Changes

Changes to Pivot-Object.ps1.

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18











19
20
21
22

23
24

25
26
27
28
29
30
31
32
33
34
35
36
37
38


39
40


41
42
43
44
45
46

47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64



65
66
67
68
69
70
71
72
73




74





75
76
77
78


79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

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

#}







|
|
|



|

|
>
>
>
>
>
>
>
>
>
>
>




>

<
>
|
|
<
|
|
|
<
<
<
|
|
<
<
<
>
>


>
>
|
<
<
<
<
<
>
|
<
<
|
<
|
<
<
|
<
<
<
|

<
<
<
<
>
>
>
|
<
<
<
<
|
<
<
<
>
>
>
>
|
>
>
>
>
>
|
<
<
<
>
>
|
|
|
|
|
<
|
<
<
<
|
|
|
<
|
|

<



<
|


|
>

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

36
37
38

39
40
41



42
43



44
45
46
47
48
49
50





51
52


53

54


55



56
57




58
59
60
61




62



63
64
65
66
67
68
69
70
71
72
73



74
75
76
77
78
79
80

81



82
83
84

85
86
87

88
89
90

91
92
93
94
95
96
# 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: 885
# x-archived: 2016-08-09T21:53:40
# x-published: 2009-02-20T22:14:00
#
#
#.Synopsis
#  Pivot multiple objects which have properties that are name, value pairs
#.Description
#  Takes a series of objects where there are multiple rows which have a pair of columns where the values are different on ech row with the name and value of an additional property, and outputs new objects which have those new properties on them.
#
#.Parameter GroupOnColumn
#  The name of the property to merge on. Items with the same value in this column will be combined.
#.Parameter NameColumn
#  The name of the property that has the names of the new properties to create.
#.Parameter ValueColumn
#  The name of the property that has the values of the new properties to create
#.Parameter InputObject
#  The input objects.  These can be passed on the pipeline as well (or instead).
#.Parameter Jagged
#  If Jagged is set, the objects that return only have the properties defined on them. Otherwise, all properties that are defined for any object are assinged (with null values) on all objects that are output.
#.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
#  12276, StdHrsPerWeek, 40,          J8329029

#  12276, UserStyle,     Fixed,       J8329029
#  2479,  LeaverId,      L1153731,    X5969731
#  2479,  LastDayOffice, 20 Feb 2009, X5969731



#"@.Split("`n") | ConvertFrom-Csv | Pivot-Objects SamAccountName Attribute Value
#



#
#
#.Notes
#  Author: Joel Bennett
#
#
# function Pivot-Object {





PARAM(
   [string]$GroupOnColumn


,  [string]$NameColumn

,  [string]$ValueColumn


,  [object[]]$InputObject=@()



,  [switch]$Jagged
)




PROCESS {
   if($_) {
      $InputObject += $_
   }




}



END {
   if($InputObject[0] -isnot [PSObject]) { 
            $first = new-object PSObject $first  
   } else { $first = $InputObject[0]   }

   [string[]]$extra = @( $first.PSObject.Properties | 
                         &{PROCESS{$_.Name}} | 
                         Where { ($columns -notcontains $_) -and 
                                 (@($NameColumn,$ValueColumn) -notContains $_)
                         } )




   [string[]]$columns = @($GroupOnColumn) + $extra +
                        @($InputObject | Select-Object -Expand $NameColumn | Sort-Object -Unique)

   ForEach($item in  $InputObject | group-Object $GroupOnColumn) {
      $thing = New-Object PSObject | 
               Add-Member -Type NoteProperty -Name $GroupOnColumn -Value $item.Name -passthru


      foreach($name in $extra) {



         Add-Member -input $thing -MemberType NoteProperty -Name $name -Value $item.Group[0].$name
      }


      foreach($i in $item.Group) { 
         Add-Member -Input $thing -Type NoteProperty -Name $i.$NameColumn -Value $i.$ValueColumn 
      }

      if($Jagged) {
         Write-Output $thing
      } else {

         Write-Output $thing | select $columns
      }
   }
   
}
#}