Joining multi-line output to a single line in a group

gvk25 · March 7, 2012, 2:55am

Hi,

My Oracle query is returing below o/p

---------------------------------------------------------- 
Ins    trnas             value 
a      lkp1               x 
a      lkp1               y 
b      lkp1               a 
b      lkp2               x 
b      lkp2               y 
------------------------------------ 
now I want to convert this o/p  to  below o/p using perl script
 
ins trans    value 
a   lkp1       xy 
b   lkp1       a 
b   lkp2       xy

Thanks
gvk25

balajesuri · March 7, 2012, 3:03am

perl -lane 'if($.==1){print;next}; $x{"$F[0] $F[1]"} .= $F[2]; END{for (sort keys %x){print "$_ $x{$_}"}}' inputfile

gvk25 · March 7, 2012, 3:45am

I am storing my o/p of the query in an array,I want to use an array instead of inputfile.
Dow we have any other way to implement above logic

@ins=(a,a,b,b,b); @trans=(lkp1,lkp1,lkp1,lkp1,lkp2,lkp2) @value(x,y,a,x,y)

balajesuri · March 7, 2012, 4:16am

#! /usr/bin/perl -w
use strict;

my @ins   = qw ( a    a    b    b    b    );
my @trans = qw ( lkp1 lkp1 lkp1 lkp2 lkp2 );
my @value = qw ( x    y    a    x    y    );
my %x;
my $prev = "";

for (0..$#ins) {
    if ($prev eq "$ins[$_] $trans[$_]") {
        print "$value[$_]";
    }
    else {
        print "\n$ins[$_] $trans[$_] $value[$_]";
        $prev = "$ins[$_] $trans[$_]";
    }    
}

gvk25 · March 7, 2012, 4:46am

thanks very much for you help

---------- Post updated at 03:16 PM ---------- Previous update was at 03:08 PM ----------

I want to call a funtion with the value of each group how can I acheive this

I mean after concatenation of particular group values

i.e  group 1    a  lkp1   xy 
                    sub test(xy)
     group 2    b  lkp2   a  
                    sub test(xy)
     group 3    b lkp2  xy
                     sub test (xy)
 
and so ....
 
a lkp1 xy
b lkp1 a
b lkp2 xy

ctsgnb · March 7, 2012, 4:50am

awk 'NR>1{x=$1 FS $2;n=NR}!b[x]++{b[n]=x}{a[x]=((x in a)?a[x]:z)$NF}END{for(i=0;++i<=n;) if(i in b) print b,a[b]}' yourfile

gvk25 · March 7, 2012, 4:52am

Can you explain me the code what you are doing

---------- Post updated at 03:22 PM ---------- Previous update was at 03:20 PM ----------

hi Balaji can you explain the logic which you have implemented.

balajesuri · March 7, 2012, 5:22am

balajesuri:

for (0..$#ins) {
   if ($prev eq "$ins[$_] $trans[$_]") { # Check if previous elements from arr1 and arr2 are same as current arr1 and arr2 values
   print "$value[$_]"; # Print arr3 value if condition is true
   }
   else {
   print "\n$ins[$_] $trans[$_] $value[$_]"; # Otherwise print all three values
   $prev = "$ins[$_] $trans[$_]"; # Store arr1 and arr2 value in $prev
   }    
}

I've assumed that all three arrays contain equal number of elements.
For each element, I check if the previous element of array1 and array2 are same as the current one. If yes, then print value from 3rd array else print values from all 3 arrays.