How do i combine multiple columns to array result?

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

Viewing 2 reply threads
  • Author
    Posts
    • #144777
      Participant
      Topics: 40
      Replies: 111
      Points: 639
      Rank: Major 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: 5
      Replies: 2369
      Points: 5,992
      Helping Hand
      Rank: Community MVP
    • #144831
      Participant
      Topics: 40
      Replies: 111
      Points: 639
      Rank: Major Contributor
Viewing 2 reply threads
  • The topic ‘How do i combine multiple columns to array result?’ is closed to new replies.