PoshCode Logo PowerShell Code Repository

Join-Collections 2.0 by Joel Bennett 3 years ago (modification of post by Joel Bennett view diff)
View followups from Joel Bennett and Jon Webster | diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1461"></script>download | new post

Performs an inner join on two collections of objects which share a common key value. Now works on DataTable objects (ie: can join the rows from two tables).

  1. #requires -version 2.0
  2.  
  3. ####################################################################################################
  4. ## Version History:
  5. ##
  6. ## Version 2.0 Added Join-Object and removed dependency on ConvertFrom-Hashtable
  7. ##             Now works on DataTables!
  8. ## Version 1.1 Fixed column uniqueness bug http://poshcode.org/1460
  9. ## Version 1.0 First post http://poshcode.org/1459
  10.  
  11. #.Note
  12. #  This script includes a Join-Object function you could use outside
  13. #.Synopsis
  14. #  Performs a inner join on two collections of objects based on a common key column.
  15. #.Description
  16. #  Takes two sets of objects where there are multiple "rows" and where each set has a shared column where the values match, and generates new objects with all the values from each.
  17. #.Parameter GroupOnColumn
  18. #  The name of the property to merge on. Items with the same value in this column will be combined.
  19. #.Parameter FirstCollection
  20. #  The first set of data
  21. #.Parameter FirstJoinColumn
  22. #  The name of the key id column in the first set
  23. #.Parameter SecondCollection
  24. #  The second set of data
  25. #.Parameter SecondJoinColumn
  26. #  The name of the matching key id column in the second set
  27. #  OPTIONAL. Defaults to the same as FirstJoinColum
  28. #.Example
  29. #  Import-CSV data.csv | Pivot-Objects SamAccountName Attribute Value
  30. #
  31. #  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.
  32. #
  33. #.Example
  34. # $FirstCollection = @"
  35. #  FirstName,  LastName,   MailingAddress,    EmployeeID
  36. #  John,       Doe,        123 First Ave,     J8329029
  37. #  Susan Q.,   Public,     3025 South Street, K4367143
  38. #"@.Split("`n") | ConvertFrom-Csv                              
  39. #
  40. # $SecondCollection = @"
  41. #  ID,    Week, HrsWorked,   PayRate,  EmployeeID
  42. #  12276, 12,   40,          55,       J8329029
  43. #  12277, 13,   40,          55,       J8329029
  44. #  12278, 14,   42,          55,       J8329029
  45. #  12279, 12,   35,          40,       K4367143
  46. #  12280, 13,   32,          40,       K4367143
  47. #  12281, 14,   48,          40,       K4367143
  48. #"@.Split("`n") | ConvertFrom-Csv                              
  49. #
  50. # Join-Collections $FirstCollection EmployeeID $SecondCollection | ft -auto        
  51. #
  52. #.Notes
  53. #  Author: Joel Bennett
  54.  
  55. # function Join-Collections {
  56. PARAM(
  57.    $FirstCollection
  58. ,  [string]$FirstJoinColumn
  59. ,  $SecondCollection
  60. ,  [string]$SecondJoinColumn=$FirstJoinColumn
  61. )
  62. PROCESS {
  63.    $ErrorActionPreference = "Inquire"
  64.    foreach($first in $FirstCollection) {
  65.       $SecondCollection | Where{ $_."$SecondJoinColumn" -eq $first."$FirstJoinColumn" } | Join-Object $first
  66.    }
  67. }
  68. BEGIN {
  69.    function Join-Object {
  70.    Param(
  71.       [Parameter(Position=0)]
  72.       $First
  73.    ,
  74.       [Parameter(ValueFromPipeline=$true)]
  75.       $Second
  76.    )
  77.    BEGIN {
  78.       [string[]] $p1 = $First | gm -type Properties | select -expand Name
  79.    }
  80.    Process {
  81.       $Output = $First | Select $p1
  82.       foreach($p in $Second | gm -type Properties | Where { $p1 -notcontains $_.Name } | select -expand Name) {
  83.          Add-Member -in $Output -type NoteProperty -name $p -value $Second."$p"
  84.       }
  85.       $Output
  86.    }
  87.    }
  88. }
  89. #}

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me