How do i combine multiple columns to array result?

Welcome Forums General PowerShell Q&A How do i combine multiple columns to array result?

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
4 months, 1 week ago.

  • Author
    Posts
  • #144777

    Participant
    Topics: 24
    Replies: 64
    Points: 378
    Rank: Contributor

    Suppose I have a table

    table

    I have the following script

    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] }
    
    $Result = Query "SELECT [databasename], [servertypeA] from table GROUP BY [databasename], [servertypeA]"
    
        $Servers = @()
        $DB = @()
    
        foreach($row in $Result)
        {   
            $i++
            $DB += $row.Item("databasename")
            $Servers += $row.Item("servertypeA") #here i want to also store for servertypeB (so that Server array would store server1,serverx,servery and in next iteration server2,serverx,servery)
    
            cmd /c "PS.bat $somescript.ps1 $($Servers[-1]) $($DB[-1])"
        }

    I would like to add servertypeB to my $Servers array, something like this:

    $Servers += $row.Item("servertypeA") + $row.Item("servertypeB")

    so that $Server array would store server1,serverx,servery and in next iteration server2,serverx,servery

    I am not sure if that is possible, and there is another issue: I would have to include servertypeB in the $Result query:

    $Result = Query "SELECT [databasename], [servertypeA], [servertypeB] from table GROUP BY [databasename], [servertypeA]"

    but wouldnt groupby select only one row because servertypeA and databasename are duplicated so it would pick distinct row, which means serverywould be left out? and if I remove groupby then its gonna select server1 and server2 twice which i dont want to have as duplicate

    I was suggested an answer as following:

    SELECT [databasename], ServerTypeName =[servertypeA], ServerTypeGroup='A' from table GROUP BY [databasename], [servertypeA]
    UNION 
    SELECT [databasename], ServerTypeName =[servertypeB], ServerTypeGroup='B' from table GROUP BY [databasename], [servertypeB]

    But i dont know how to integrate that with the script i have. am i supposed to use the identifiers in row.item?

  • #144788

    Participant
    Topics: 1
    Replies: 1430
    Points: 2,130
    Helping Hand
    Rank: Community Hero
  • #144831

    Participant
    Topics: 24
    Replies: 64
    Points: 378
    Rank: Contributor

The topic ‘How do i combine multiple columns to array result?’ is closed to new replies.