Transpose columns to rows

PowerShell Help
1

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

2

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

Powershell Script to transpose rows into columns

3

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

4

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