awk to compare fields of multiple lines and combine

Question

I have data in file with pattern as shown below -

  TRSSOEC TRBSOEC      ADICOES                        DELETE            TROEC
  TRSSOEC TRBSOEC      ADICOES                        INSERT            TROEC
  TRSSOEC TRBSOEC      ADICOES                        SELECT            TROEC
  SOBCXYZ SCHEMA1      AGENTES_CARGA                  INSERT            TROEC
  SOBCXYZ SCHEMA1      AGENTES_CARGA                  SELECT            TROEC
  YASU999 ASDF123      BS_ADICOES_SISCOMEX            UPDATE            TRBSOEC
  USER123 WAREHOU      BS_ADICOES_TRIBUTOS_BKP        DELETE            TRBSOEC
  USER123 WAREHOU      BS_ADICOES_TRIBUTOS_BKP        INSERT            TRBSOEC

I would like to compare lines with field 1,2,3 and if they are same then capture field 4 to diplay as shown below -

grant DELETE,INSERT,SELECT on TRBSOEC.ADICOES to TRSSOEC;
grant INSERT,SELECT on SCHEMA1.AGENTES_CARGA to SOBCXYZ;
grant UPDATE on ASDF123.BS_ADICOES_SISCOMEX to YASU999;
grant DELETE,INSERT on WAREHOU.BS_ADICOES_TRIBUTOS_BKP to USER123;

Basically logic is to group based on field 1,2,3 and print distinct field 4 along with other common fields. grant <comma separated array of filed 4> on filed2.field3 to field1 ;

So far I tried below but not sure how to capture field 4, any guidance would be helpful.

awk '
    # Each line
    {
        if ($1 == prev[1] && $2 == prev[2] && $3 == prev[3])
        {
            # Capture and use field 4 of last match
            if(!nrMatched) { nrMatched = prev[4] }
            print "grant " $nrMatched " on " $2"."$3 " to " $1";"
        }
        else
        {
            # No match, reset matched flag and just copy record through
            nrMatched = 0
        }
    
        # Save fields from this line for next comparison
        split($0, prev)
    }
' test.txt 

Answer 1

Using any awk and assuming the input is grouped by the first 3 fields as shown in the sample input and as the OPs code requires:

$ cat tst.awk
{ tgt = "on " $2 "." $3 " to " $1 ";" }
tgt != prev {
    if ( NR > 1 ) {
        print grant, prev
    }
    prev = tgt
    grant = "grant " $4
    next
}
{ grant = grant "," $4 }
END { print grant, prev }

$ awk -f tst.awk file
grant DELETE,INSERT,SELECT on TRBSOEC.ADICOES to TRSSOEC;
grant INSERT,SELECT on SCHEMA1.AGENTES_CARGA to SOBCXYZ;
grant UPDATE on ASDF123.BS_ADICOES_SISCOMEX to YASU999;
grant DELETE,INSERT on WAREHOU.BS_ADICOES_TRIBUTOS_BKP to USER123;

Answer 2

What I would do:

awk '
{
    key = $1"|"$2"|"$3
    if (!(key in ops)) {
        groups[key]=$1","$2","$3
    }
    if (!seen[key"|"$4]) {
        ops[key]=ops[key](ops[key] ? "," : "") $4
        seen[key"|"$4]=1
    }
}
END {
    for (key in groups) {
        split(groups[key], fields, ",")
        print "grant " ops[key] " on " fields[2] "." fields[3] " to " fields[1]";"
    }
}' file