Hello Guru's,

EDIT: I do have an awk script that does most of the job for me. An equivalent to this awk script would be just fine. 

awk '{t=$1;gsub(/"/,"",t);a[t]=a[t] FS $0} END {for (i in a) print a[i]}' filename

Making sure I post under the right category...windows powershell!

I have an awk script that does the job somewhat but I need a powershell script that transforms rows into columns.

Basically I need to join the rows into columns if the first column has the same value. 

Below is the sample input file.
A 1,2,3
A 4,5,6
B 1,2,3
B 4,5,6
C 1,2,3
C 2,3,4
C 3,4,5

Here is the desired output: 
A 1,2,3,4,5,6
B 1,2,3,4,5,6
C 1,2,3,2,3,4,3,4,5

Cheers for any help :-)

• Edited by Knowledge_is_King Tuesday, September 08, 2015 2:54 AM more information

This works for me (last refactored 9/9/2015) :

$lines = gc input.txt

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $n = 0
  foreach ($token in $tokens) {
    if ($n -ne 0) {
      if ( ( '' + ((get-variable -name $('__' + $tokens[0]) -ea 0).value) -eq '') ) {
        $d = ''
       }
      else {
        $d = ','
       }
      set-variable -name $('__' + $tokens[0]) -value (
         '' + ((get-variable -name $('__' + $tokens[0]) -ea 0).value) + $d + $token )
     } 
    $n++
   }
 }

$o = @()

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $o += $tokens[0] + ' ' + (get-variable -name $('__' + $tokens[0]) -ea 0).value
 }

$o | Select-Object -Unique

• Edited by LarryWeiss 18 hours 7 minutes ago

This works for me (last refactored 9/9/2015) :

$lines = gc input.txt

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $n = 0
  foreach ($token in $tokens) {
    if ($n -ne 0) {
      if ( ( '' + ((get-variable -name $('__' + $tokens[0]) -ea 0).value) -eq '') ) {
        $d = ''
       }
      else {
        $d = ','
       }
      set-variable -name $('__' + $tokens[0]) -value (
         '' + ((get-variable -name $('__' + $tokens[0]) -ea 0).value) + $d + $token )
     } 
    $n++
   }
 }

$o = @()

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $o += $tokens[0] + ' ' + (get-variable -name $('__' + $tokens[0]) -ea 0).value
 }

$o | Select-Object -Unique

• Edited by LarryWeiss 18 hours 13 minutes ago

This works for me (last refactored 9/9/2015) :

$lines = gc input.txt

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $n = 0
  foreach ($token in $tokens) {
    if ($n -ne 0) {
      if ( ( '' + ((get-variable -name $('__' + $tokens[0]) -ea 0).value) -eq '') ) {
        $d = ''
       }
      else {
        $d = ','
       }
      set-variable -name $('__' + $tokens[0]) -value (
         '' + ((get-variable -name $('__' + $tokens[0]) -ea 0).value) + $d + $token )
     } 
    $n++
   }
 }

$o = @()

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $o += $tokens[0] + ' ' + (get-variable -name $('__' + $tokens[0]) -ea 0).value
 }

$o | Select-Object -Unique

• Edited by LarryWeiss Wednesday, September 09, 2015 12:54 PM

And a cleaner version that uses a hashtable:

$lines = gc input.txt

$table = @{}

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $n = 0
  foreach ($token in $tokens) {
    if ($n -ne 0) {
      if ( ( ('' + ($table.($tokens[0])) ) -eq '') ) {
        $d = ''
       }
      else {
        $d = ','
       }
      $table.($tokens[0]) = '' + ($table.($tokens[0])) + $d + $token
     } 
    $n++
   }
 }

$o = @()

foreach ($line in $lines) {
  $tokens = $line -split '[ ,]'
  $o += $tokens[0] + ' ' + ($table.($tokens[0]))
 }

$o | Select-Object -Unique