Transpose columns to rows

Welcome Forums General PowerShell Q&A Transpose columns to rows

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

 
Participant
3 months, 2 weeks ago.

  • Author
    Posts
  • #110246

    Participant
    Points: 1
    Rank: Member

    Hi all,

    I have a table with 2 columns and a few rows

    I would like to transpose the colums to rows.

    Row1:Name,Group
    Row2:John,GroupA
    Row3:Jane,GroupB
    Row4:Mike,GroupC
    Row5:James,GroupC

    Desired result:

    Row1: GroupA,GroupB,GroupC,GroupC
    Row2: John,Jane,Mike,James

    I've tried something like this but it just gives me 2 columns, with Name and one of the groups.

    $a = Import-Csv "C:\groups.csv"
    
    $Duration = Measure-Command {
    
    $b = @()
    
    foreach ($Name in $a.Name | Select -Unique) {
    
    $Props = [ordered]@{ Name = $Name }
    
    foreach ($Group in $a.Group | Select -Unique){
    
    $Value = ($a.where({ $_.Group -eq $Group })).Value
    
    $Props += @{ $Group = $Value }
    
    $b += New-Object -TypeName PSObject -Property $Props
    
    }
    
    }
    
    }
    
    $b | FT -AutoSize
    
    

    Thanks

  • #110248

    Participant
    Points: 261
    Helping Hand
    Rank: Contributor

    Did you try to search for? If I do I find this as the first hit:

    Powershell Script to transpose rows into columns

    • #110252

      Participant
      Points: 1
      Rank: Member

      Hi Olaf,

      Yeah, that's what I used. Swapping Group for Server as in the technet post. My script's not 100% there yet though.

      Thanks

  • #110258

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    You have an object...

    $obj = @()
    $obj += [pscustomobject]@{'Name'='John';'Group'='GroupA'}
    $obj += [pscustomobject]@{'Name'='Jane';'Group'='GroupB'}
    $obj += [pscustomobject]@{'Name'='Mike';'Group'='GroupC'}
    $obj += [pscustomobject]@{'Name'='James';'Group'='GroupD'}
    

    that looks like this:

    PS C:\Users\Rob> $obj
    
    Name  Group 
    ----  ----- 
    John  GroupA
    Jane  GroupB
    Mike  GroupC
    James GroupD
    

    Based on what you posted, you want to convert object properties into arrays. Let's look at how to convert a single object property into an array:

    # Use Select-Object to Expand the property
    $names = $obj | Select-Object -ExpandProperty Name
    # Use an implicit for each loop
    $names = $obj.Name
    # Use an explicit for each loop
    $names = $obj | foreach{$_.Name}
    

    All of these convert the Name property\column into an array:

    PS C:\Users\Rob> $names
    John
    Jane
    Mike
    James
    

    If you wanted to do this for every property in the object, then you can do a loop for every property and create a new array variable. This would create new variables called $name and $group, but could difficult to find since we are generating them dynamically, so this will append a double underscore:

    foreach ($item in $obj[0].PSObject.Properties) {
        Set-Variable -Name ('__{0}' -f $item.Name) -Value ($obj | Select-Object -ExpandProperty $($item.Name))
    }
    

    After executing, now you can find the variables with Get-Variable using a wildcard search:

    PS C:\Users\Rob> Get-Variable -Name '__*'
    
    Name                           Value                                                                                                                                                                                   
    ----                           -----                                                                                                                                                                                   
    __Group                        {GroupA, GroupB, GroupC, GroupD}                                                                                                                                                        
    __Name                         {John, Jane, Mike, James}                                                                                                                                                               
    
    
    
    PS C:\Users\Rob> $__Group
    GroupA
    GroupB
    GroupC
    GroupD                                                                                                                                                            
    

The topic ‘Transpose columns to rows’ is closed to new replies.