Poweshell retrieve sql query result

I am quite new to PowerShell. I have multiple SQL queries which I am executing from a PS script using SQL adapter. I need to retrieve the common results based on a column from the queries without changing existing queries or writing another one. How to achieve this with PowerShell using the results stored in a variable? I’ve tried working around with rows[x],columns[y] but it doesn’t work and writing the names of the columns every time is difficult for big results.

I am storing the results this way:

$SqlAdapter.SelectCommand = $SqlCmd
$Dataset = New-Object System.Data.Dataset
$SqlAdapter.Fill($Dataset)
$DataSet.Tables[0]

Suppose query 1 results:

 names   type1  id1
 pk1     t1   26
 pk3     t5   25
 pk26    t5   36 
 pk11    t1   99

query 2 results:

 names  type2  id2
 pk11   t9   2699
 pk1    t7   252
 pk136  t9   368 
 pk8    t5   9

I need the common one’s from both results based on the names column which I will further save in a file. (Edited the output as it wasn’t in right format)

 names   type1  id1 type2 id2
 pk1     t1     26  t7    252
 pk11    t1     99  t9    2699

Answer

In general, PowerShell’s Group-Object coupled with array indexing can achieve this goal.

# Objects with query results
$q1 = @'
names,type,id
pk1,t1,26
pk3,t5,25
pk26,t5,36
pk11,t1,99
'@ | ConvertFrom-Csv

$q2 = @'
names,type,id
pk11,t9,2699
pk1,t7,252
pk136,t9,368
pk8,t5,9
'@ | ConvertFrom-Csv

0..1 | Foreach-Object {
    $i = $_ # Index of each grouped item
    # Looking for count = 2 to find matches from both queries
    $q1 + $q2 | Group names | Where Count -eq 2 | Foreach-Object {
        # i = 0 is common objects from $q1
        # i = 1 is common objects from $q2
        $_.Group[$i]
    } | Export-Csv Results$i.csv -NoType
}

Results0.csv will contain the first table results. Results1.csv will contain the second table results.

Group-Object groups objects based on grouped properties. Grouping on names will put all objects with the same names value into a GroupInfo object accessible by the Group property. The Group property is a collection with indexed items. The first item in each grouping ([0] item) will be the first object read. So as long as $q1 and $q2 share a value, then [0] item will always be from $q1 because we send $q1 into the pipeline first from expression $q1 + $q2. Correspondingly, [1] will contain $q2 items.

---

Edit 1: You updated your question with table column names with a different requirement.

# Objects with query results
$q1 = @'
names,type1,id1
pk1,t1,26
pk3,t5,25
pk26,t5,36
pk11,t1,99
'@ | ConvertFrom-Csv

$q2 = @'
names,type2,id2
pk11,t9,2699
pk1,t7,252
pk136,t9,368
pk8,t5,9
'@ | ConvertFrom-Csv

$q1+$q2 | Group-Object names |
    where count -eq 2 | Foreach-Object {
        $hash = [ordered]@{}
        $_.Group | Foreach-Object {
            $_.psobject.properties | Foreach-Object {
                $hash[$_.Name] = $_.Value
            }
        }
    [pscustomobject]$hash
    } | Export-Csv results.csv -NoType