powershell - PowerShell - 用重复行扩展数组中的数组列

Question

我有一个System.Array名为 的对象$Data，第一个 ([0]) 项如下所示：

RecordDate              : {43739, 43740, 43741, 43742...}
MAX_LAST_UPDATE_DATE    : 30/10/2019 14:08:33
EMPLOYEE_NUMBER         : 1000522
EFFECTIVE_START_DATE    : 01/10/2019 00:00:00
EFFECTIVE_END_DATE      : 31/12/4712 00:00:00
CC                      : 0726
REGION_NAME             : Head Office
LOCATION_NAME           : Inventory
FIRST_NAME              : Name
MIDDLE_NAMES            : Mid
LAST_NAME               : Last
KNOWN_AS                : NickName
JOB_TITLE               : Inventory Manager
WORK_NUMBER             : 
Employment Category     : Full Time
NORMAL_HOURS            : 40
GROUP_NAME              : Indirect
Manager Employee Number : 1034422
PERSON_TYPE             : Employee
HIRE_DATE               : 16/11/1983 00:00:00
TERMINATION_DATE        : 
DATE_OF_BIRTH           : 23/05/1966 00:00:00
NATIONAL_IDENTIFIER     : 111

我正在尝试对整个数组上的第一列“RecordDate”进行反透视，如下所示：

RecordDate              : 43739
MAX_LAST_UPDATE_DATE    : 30/10/2019 14:08:33
EMPLOYEE_NUMBER         : 1000522
EFFECTIVE_START_DATE    : 01/10/2019 00:00:00
EFFECTIVE_END_DATE      : 31/12/4712 00:00:00
CC                      : 0726
REGION_NAME             : Head Office
LOCATION_NAME           : Inventory
FIRST_NAME              : Name
MIDDLE_NAMES            : Mid
LAST_NAME               : Last
KNOWN_AS                : NickName
JOB_TITLE               : Inventory Manager
WORK_NUMBER             : 
Employment Category     : Full Time
NORMAL_HOURS            : 40
GROUP_NAME              : Indirect
Manager Employee Number : 1034422
PERSON_TYPE             : Employee
HIRE_DATE               : 16/11/1983 00:00:00
TERMINATION_DATE        : 
DATE_OF_BIRTH           : 23/05/1966 00:00:00
NATIONAL_IDENTIFIER     : 111

RecordDate              : 43740
MAX_LAST_UPDATE_DATE    : 30/10/2019 14:08:33
EMPLOYEE_NUMBER         : 1000522
EFFECTIVE_START_DATE    : 01/10/2019 00:00:00
EFFECTIVE_END_DATE      : 31/12/4712 00:00:00
CC                      : 0726
REGION_NAME             : Head Office
LOCATION_NAME           : Inventory
FIRST_NAME              : Name
MIDDLE_NAMES            : Mid
LAST_NAME               : Last
KNOWN_AS                : NickName
JOB_TITLE               : Inventory Manager
WORK_NUMBER             : 
Employment Category     : Full Time
NORMAL_HOURS            : 40
GROUP_NAME              : Indirect
Manager Employee Number : 1034422
PERSON_TYPE             : Employee
HIRE_DATE               : 16/11/1983 00:00:00
TERMINATION_DATE        : 
DATE_OF_BIRTH           : 23/05/1966 00:00:00
NATIONAL_IDENTIFIER     : 111

RecordDate              : 43741
MAX_LAST_UPDATE_DATE    : 30/10/2019 14:08:33
...

有没有办法偷偷摸摸地做到这一点，Select -expandproperty或者做与Group-Object能力相反的事情？不做for($i)和for($j)循环的组合？

在 Excel PowerQuery 中的表格上非常简单，只需单击展开并瞧瞧。

问候， 贾雷克

标签： powershell

Answer 1

您可以结合Select-Object -ExpandProperty通用-PipelineVariable参数和克隆（PSv3+ 语法）：

对于输入集合[pscustomobject]或[hashtable]实例：

# Sample input array of custom objects to expand by .RecordDate
$array =
  [pscustomobject] @{ RecordDate = 1, 2; OtherProp1 = 'one'; OtherProp2 = 'two' },
  [pscustomobject] @{ RecordDate = 3, 4; OtherProp1 = 'three'; OtherProp2 = 'four' }

# Write the array elements to the pipeline, and store each in variable
# $objectOrHashtable for use in a later pipeline segment.
Write-Output $array -PipelineVariable objectOrHashtable |
  # Expand the input object's .RecordData property, i.e. send its
  # elements one by one to the next pipeline segment.
  Select-Object -ExpandProperty RecordDate | 
    ForEach-Object {
      # Clone the original input object.
      $clone = if ($objectOrHashtable -is [Management.Automation.PSCustomObject]) {
        $objectOrHashtable.psobject.Copy()
      } else { # assume [hashtable] or a type that implements [System.ICloneable]
        $objectOrHashtable.Clone()
      }
      # Assign the record date at hand to the clone...
      $clone.RecordDate = $_
      # ... and output it.
      $clone
    }

以上产生以下; 请注意，基于枚举输入对象.RecordDate数组的元素同时保留所有其他属性，输出了 4 个对象：

RecordDate OtherProp1 OtherProp2
---------- ---------- ----------
         1 one        two
         2 one        two
         3 three      four
         4 three      four

笔记：

• 以上适用于两种类型的输入对象：

  • 自定义对象（[pscustomobject]实例，例如 created by Import-Csv）

    • 注意：出于技术原因，您不能使用-is [pscustomobject]，而必须使用完整的类型名称，System.Management.Automation.PSCustomObject（System.前缀可以省略）；[pscustomobject]，由于历史原因，与[psobject]( System.Management.Automation.PSObject)相同，对于不是自定义对象-is [psobject]的对象也是如此。

  • 哈希表（System.Collections.Hashtable实例 - 但不是[ordered]哈希表）；更一般地说，任何实现System.ICloneable.

• 对自定义对象和哈希表执行的克隆很浅（成员方面），但使用标量字符串和数值就足够了。

  • 通常，该ICloneable接口没有规定克隆行为的细节，这就是为什么通常不鼓励使用它的原因。

---

对于[System.Data.DataRow]实例的输入集合：

System.Data.DataRow克隆实例集合——数据表的行System.Data.DataTable——需要自定义克隆逻辑，但输出的方法和结构基本相同：

# Create a sample DataTable...
$dt = [System.Data.DataTable]::new('sample')
# ... define the columns ...
$dt.Columns.AddRange([System.Data.DataColumn[]] (
  @{ ColumnName = 'RecordDate'; DataType = [object[]] },
  @{ ColumnName = 'OtherProp1'; DataType = [string] },
  @{ ColumnName = 'OtherProp2'; DataType = [string] }
))
# ...and add sample rows.
@{ RecordDate = 1, 2; OtherProp1 = 'one'; OtherProp2 = 'two' },
@{ RecordDate = 3, 4; OtherProp1 = 'three'; OtherProp2 = 'four' } | % {
  $dt.Rows.Add(($dr = $dt.NewRow()))
  foreach ($entry in $_.GetEnumerator()) {
    $dr[$entry.Key] = $entry.Value 
  }  
}

# Create an auxiliary, empty clone of the input data table
# to facilitate cloning of individual rows.
$dtAux = $dt.Clone()

# Write the data rows to the pipeline, and store each in variable
# $obj for use in a later pipeline segment.
Write-Output $dt.Rows -PipelineVariable row |
  # Expand the input object's .RecordData property, i.e. send its
  # elements one by one to the next pipeline segment.
  Select-Object -ExpandProperty RecordDate |
    ForEach-Object {
      # Clone the data row at hand.
      $dtAux.Clear(); $dtAux.ImportRow($row)
      $clone = $dtAux.Rows[0]
      # Assign the record date at hand to the clone...
      $clone.RecordDate = @($_)
      # ... and output it.
      $clone
    }