none
How do i combine multiple columns to array result?

    Question

  • 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

    Thursday, March 14, 2019 5:53 PM

Answers

All replies

  • The code and question do not make much sense.   It seems that there would be two tables or that you need a SQL "WHERE" clause selecting bot servers.


    \_(ツ)_/

    Thursday, March 14, 2019 6:57 PM
    Moderator
  • The code and question do not make much sense.   It seems that there would be two tables or that you need a SQL "WHERE" clause selecting bot servers.


    \_(ツ)_/

    Can you elaborate ? 
    Thursday, March 14, 2019 8:59 PM
  • The code and question do not make much sense.   It seems that there would be two tables or that you need a SQL "WHERE" clause selecting bot servers.


    \_(ツ)_/

    Can you elaborate ? 

    Not until you can ask the question in a way that makes sense.  Are you asking how to update the table?  Are you asking how to add a ro to the table?  Are you asking how to create an array?

    You have to be clear.


    \_(ツ)_/

    Thursday, March 14, 2019 10:11 PM
    Moderator
  • The code and question do not make much sense.   It seems that there would be two tables or that you need a SQL "WHERE" clause selecting bot servers.


    \_(ツ)_/

    Can you elaborate ? 

    Not until you can ask the question in a way that makes sense.  Are you asking how to update the table?  Are you asking how to add a ro to the table?  Are you asking how to create an array?

    You have to be clear.


    \_(ツ)_/

    So right now my script stores servertypeA in $ servers array. So in first iteration it stores server1, db1, next iteration server2 db2 (the db's of course are stored in $DB array. Everything is good and I just want to get data also for server typeB. So in first iteration it would be server1, serverx, servery. Iteration 2 server2, serverx, servery. But idk how to do that. Of I simply say row.item(”servertypeA”)+row.item(servertypeB) , $Server[-1] outputs server1serverxservery. It should be an index for each server, not one index for 3 servers. 

    Thursday, March 14, 2019 11:19 PM
  • Sorry but that makes even less sense.

    Why do you need to do this.  Just write the SQL to return the data the way you need it.


    \_(ツ)_/

    Friday, March 15, 2019 12:02 AM
    Moderator
  • Sorry but that makes even less sense.

    Why do you need to do this.  Just write the SQL to return the data the way you need it.


    \_(ツ)_/

    I am passing the server parameter to another script, along with each database name. That other script retrieves the dynamic views of the database usage information,  like sessions, users, etc...

    That script runs every 5 minutes and generates for us a report that gets sent to business exeexecuti to track the application usage. 

    That's the background about this whole thing. 

    Right now the script I have here passes parameters only for servertypeA and the databasenane on that server. But today I was thinking about it and realized that the servertypeB information should also be reported to the executives otherwise they wouldnt really have accurate usage information. 

    All I want to do is somehow add the server typeB column to this server parameter so that the report also generates usage information for these extra servers. 

    Friday, March 15, 2019 12:25 AM
  • Sorry but that doesn't explain anything.  I am sure you know what you want but nothing you have posted defines, technically, what you are asking.

    I think I will just bow out.  Maybe someone else will understand what you are trying to ask.


    \_(ツ)_/

    Friday, March 15, 2019 12:27 AM
    Moderator
  • Sorry but that doesn't explain anything.  I am sure you know what you want but nothing you have posted defines, technically, what you are asking.

    I think I will just bow out.  Maybe someone else will understand what you are trying to ask.


    \_(ツ)_/

    Should I post this in SQL server?

    Friday, March 15, 2019 12:29 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, April 9, 2019 1:46 PM
    Moderator
  • $Servers += $row.Item("servertypeA") 
    $Servers += $row.Item("servertypeB")
    • Marked as answer by cataster Tuesday, April 9, 2019 3:26 PM
    Tuesday, April 9, 2019 3:26 PM