using awk or sed to create new lines based on extraneous columns

Question

I have a file that looks like this (columns are separated by tabs) :

Owner    Phone    Car
Dan    111-111-1111    Avalon    Camry
Stacy    222-222-2222    Corolla    Yaris    Camry
Henry    333-333-3333    Prius

I want all the extra cars to be on their own line below the original (with the owner and phone information copied for every additional car) so it will look like this:

Owner    Phone    Car
Dan    111-111-1111    Avalon
Dan    111-111-1111    Camry
Stacy    222-222-2222    Corolla        
Stacy    222-222-2222    Yaris
Stacy    222-222-2222    Camry
Henry    333-333-3333    Prius

I was wondering if there is a way to implement this using awk or sed (a one liner preferably).

Answer 1

With awk you can can loop through the fields from 3 to end and print them along with field 1 and 2:

awk '{for(i=3;i<=NF;i++){print $1,$2,$i}}' file

Btw, if you want the output looking nicely aligned you can pipe to the column command:

awk '{for(i=3;i<=NF;i++){print $1,$2,$i}}' file | column -t

Answer 2

With sed: repeatedly replace tabe with newlines and back to get the delimited words, and append each subsequent field before printing the pattern space up to the first embedded newline.

sed -n -e 's/\t/\n/g' -e 's/\n/\t/' -e 's/\n/\t/' -eP -e:l -e 's/\n/\t/' -eta -eb -e:a -e 's/\([^\t]*\t[^\t]*\)\t[^\t]*\(\t[^\t]*\)$/\1\2/' -etb -eb -e:b -eP -ebl < file

The awk way is much easier :)