Q:

How to create a pivot table

I have a data file like this:
id1    name1 cat1  catname1
id1    name1 cat2  catname2
id2    name2 cat3  catname3
id3    name3 cat1  catname1
id3    name3 cat4  catname4

I would like to generate the following:
id1    name1 cat1:catname1, cat2:catname2
id2    name2 cat3:catname3
id3    name3 cat1:catname1, cat4:catname4

Is there a way to do that with AWK?

A:

Try this:
$ awk '
(($1 FS $2) in a) { 
    a[$1 FS $2] = a[$1 FS $2] ", " $(NF-1) ":" $NF
    next 
}
{ a[$1 FS $2] = $(NF-1) ":" $NF }
END { 
    for(x in a) 
        print x, a[x] 
}' file
id2 name2 cat3:catname3
id1 name1 cat1:catname1, cat2:catname2
id3 name3 cat1:catname1, cat4:catname4

If order is important, you can try this gnu-awk alternate:
$ gawk '
(($1 FS $2) in a) { 
    a[$1 FS $2] = a[$1 FS $2] ", " $(NF-1) ":" $NF
    next 
}
{ a[$1 FS $2] = $(NF-1) ":" $NF }
END { 
    n = asorti(a,s)
    for(x=1; x<=n; x++) 
        print s[x], a[s[x]] 
}' file
id1 name1 cat1:catname1, cat2:catname2
id2 name2 cat3:catname3
id3 name3 cat1:catname1, cat4:catname4

