Given 2 Tables, How to consolidate Hierarchy in a table and loop through it?

PowerShell Help
1

Given the following 2 tables:

GroupsHierarchyTable

AD/Profile Name     Org Level   Org Member
AD_Group1           Region      China
AD_Group1           Region      USA
AD_Group2           Sub Region  China
AD_Group2           Sub Region  Corp
AD_Group3           Sub Region  USA

and OrgTable

Region     Sub Region      Org Code
China      China           m500_ABC
USA        Corp            1098
USA        USA             12345
China      Corp            123_KL
USA        Corp            Z45557
China      Corp            f908L_P
China      China           234G

How do I consolidate the Org Members under 1 AD/Profile Name Org Level hierarchy?

I currently use the following code to loop through a CSV file to consolidate the Org Codes, but the requirements changed and I’d like to change the program to parse a table instead of the CSV file.

As you can see, $row.‘Org Member’ -split ‘\r?\n’ splits the Org Members because they are separated in the CSV file by a new line. With the new requirement, instead of the Org Members being split by a delimiter (i.e. new line) we replicated the Profile Names and Org Levels on separate rows for more than 1 Org Member belonging to that same hierarchy.

Function Query($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.Connection = $SqlConnection 
$SqlCmd.CommandText = $Query 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet 
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close() 
$DataSet.Tables[0] }
 
$table = Query "SELECT * FROM [dbo].[OrgTable]"
#$table.'Sub Region'.Item(1)
 
$clmns = Import-Csv .\File1.csv
 
$pscoArray = @()
 
foreach ($row in $clmns) {
    $ADGroup = $row.'AD/Profile Name'
    $OrgLevel = $row.'Org Level'
    $data = $table | Where {$_.$OrgLevel -in ($row.'Org Member' -split '\r?\n') }
    $pscoArray += [pscustomobject]@{'AD Group' = $ADGroup; 'Org Codes' = $data.'Org Code' -join '|'}
}
 
Write-SqlTableData -ServerInstance $Server -DatabaseName $Database -InputData $pscoArray -SchemaName "dbo" -TableName"NewTable"

current input: CSV file:

AD/Profile Name,Org Level,Org Member
AD_Group1,Region,"China
USA"
AD_Group2,Sub Region,"China
Corp"
AD_Group3,Sub Region,USA

Expected Output:

NewTable:

AD Group        Org Codes
AD_Group1       M500_ABC|1098|12345|123_KL|Z45557|f908L_P|234G|
AD_Group2       M500_ABC|1098|123_KL|Z45557|f908L_P|234G|
AD_Group3       12345|