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

1 week, 3 days ago.

  • Author
  • #144777

    Points: 286
    Rank: Contributor

    Suppose I have a 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 
    $DataSet.Tables[0] }
    $Result = Query "SELECT [databasename], [servertypeA] from table GROUP BY [databasename], [servertypeA]"
        $Servers = @()
        $DB = @()
        foreach($row in $Result)
            $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]
    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

    Points: 1,171
    Helping Hand
    Rank: Community Hero
  • #144831

    Points: 286
    Rank: Contributor

You must be logged in to reply to this topic.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort