column to rows based on another column...

Shell Programming and Scripting
1

Guys,
i have a file in below format where the barcode's are uniq per site but could be repeated for different site. so i want to convert the site column to rows based on the barcode's as below output.
your help is appreciated!!!

input:
SITE BARCODE QTY SP CP
10001 6281103890017 10 50 48
10001 6281103890031 2 12 10
10001 6281103890024 5 25 22
10002 6281103890017 13 50 48
10002 6281103890031 1 12 10
10002 6281103890024 2 25 22
10003 6281103890017 20 50 48
10003 6281103890031 4 12 10
10003 6281103890024 10 25 22
output:
10001 10002 10003
BARCODE QTY SP CP QTY SP CP QTY SP CP
6281103890017 10 50 48 13 50 48 20 50 48
6281103890031 2 12 10 1 12 10 4 12 10
6281103890024 5 25 22 2 25 22 10 25 22

2 
while(<DATA>){
	chomp;
	my @tmp=split;
	if($.==1){
		$header=sprintf("%s %s %s %s %s %s %s %s %s %s ",$tmp[1],$tmp[2],$tmp[3],$tmp[4],$tmp[2],$tmp[3],$tmp[4],$tmp[2],$tmp[3],$tmp[4]);
		next;
	}
	$site{$tmp[0]}=$.;
	$hash{$tmp[1]}.=$tmp[2]." ".$tmp[3]." ".$tmp[4]." ";
	$seq{$tmp[1]}=$.;	
}
print join " ", sort {$site{$a}<=>$site{$b}} keys %site;
print "\n$header\n";
for my $id (sort {$seq{$a}<=>$seq{$b}} keys %hash){
	print $id," ",$hash{$id},"\n";
}
__DATA__
SITE BARCODE QTY SP CP 
10001 6281103890017 10 50 48
10001 6281103890031 2 12 10
10001 6281103890024 5 25 22
10002 6281103890017 13 50 48
10002 6281103890031 1 12 10
10002 6281103890024 2 25 22
10003 6281103890017 20 50 48
10003 6281103890031 4 12 10
10003 6281103890024 10 25 22
3

hello Summer_Cherry...

Thanks for your reply but i am new to Unix scripting.
In the sample file, there are only three site's but normally i have around 12 site's and many barcode's for each site. May be, it was my mistake not making things clear when i posted at first. so is the code modifiable?

4

The below script will append the values based on barcode. You may need to work on the header records.

 
val=`head -1 infile1`
grep -v "$val" infile1  >infile
while read record
do
a=`echo $record | cut -d " " -f2`
b=`echo $record | cut -d " " -f3-5`
x=`grep $a outfile | cut -d " " -f1`
if test "$x" -ne "$a"
then
echo $a $b >>outfile
else
sed "s/.*$x.*/& $b/" outfile >tmp
mv tmp outfile
fi
done<infile
5

Probably summer_cherry has got your req wrong...
You can try this also..

awk ' {a[$2]=a[$2]" "$3" "$4" "$5} END {for (i in a) print i, a}' inputfile
6

Thanks Guys,

it gave me exactly as i wanted it but is it possible to arrange the column headers?

---------- Post updated 07-08-09 at 01:28 PM ---------- Previous update was 07-07-09 at 03:50 PM ----------

i just did the below for the header format, thanks guys for the support...

awk 'BEGIN {print "\t\t","10001","\t\t\t","10002","\t\t\t","10003","\n",
"BARCODE","\t","QTY","\t","SP","\t","CP","\t",
"QTY","\t","SP","\t","CP","\t","QTY","\t","SP","\t","CP"}
{if(NR==1) next; a[$2]=a[$2]"\t"$3"\t"$4"\t"$5} END {for (i in a) print i, a}' inputfile