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
Advertisement
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